Excel Cheat Sheet

Syntax & Referencing Concepts

Absolute Cell Referencing ($): Locks a cell reference so it doesn't change when you copy or drag a formula. Think of the $ as putting glue on a cell address to hold it firmly in place.

The Problem: Look at the visual table. To get the "Total" in cell B2, you might write the formula =A2*C1. This works for the first row. But when you drag that formula down to cell B3, Excel automatically changes it to =A3*C2, which is wrong because the VAT rate is not in C2.

The Solution: To fix this, you must "lock" the reference to the VAT cell. In cell B2, you change the formula to =A2*$C$1. Now, when you drag it down to B3, the formula correctly becomes =A3*$C$1. The part without the dollar sign (A3) changes, but the part with the dollar signs ($C$1) stays locked.

Visual Example: Here's how it looks in a spreadsheet. The formula in cell B2 is dragged down to B3 and B4. Notice how the reference to $C$1 (the VAT rate) never changes.

	A	B	C
1	Price	Total	20%
2	100	=A2*$C$1
3	150	=A3*$C$1

Types of Locking:
* A1 (Fully Relative): Both column and row change when dragged. This is the default.
* $A$1 (Fully Absolute): Both column and row are locked. The formula will always point to cell A1, no matter where you copy it.
* $A1 (Mixed): The column is locked, but the row can change. Useful for dragging formulas down a column while always referring to column A.
* A$1 (Mixed): The row is locked, but the column can change. Useful for dragging formulas across a row while always referring to row 1.

Example: In our visual table, the formula is =A2*$C$1. When you drag it down one row, it becomes =A3*$C$1. The A2 reference shifted to A3 because it was relative, but the $C$1 stayed completely locked.

Common question: If a question asks you to modify a formula like =COUNTIFS(C2:C101;F2) so it can be safely dragged down without shifting the source data range, you add dollar signs to lock the range coordinates: =COUNTIFS($C$2:$C$101;F2).

Excel Functions

IF: Asks a "true or false" question about a cell. It gives one answer if the question is true, and a different answer if it's false.
Simple Example: =IF(A1>50, "Pass", "Fail") checks if the score in cell A1 is greater than 50. If it is, the cell shows "Pass"; otherwise, it shows "Fail".

Example: =IF(C2<>$F$7;"negative";"positive") checks if the value in C2 is NOT EQUAL to the value in F7. If they are different, it prints "negative". If they are the same, it prints "positive".
AVERAGE: Calculates the mean (the most common type of average) for a group of numbers.
Example: =AVERAGE(B2:B101) finds the average value of all data from cell B2 down to B101.
STDEV.S: Measures how spread out your numbers are from the average. A low number means the data is tightly clustered, while a high number means it's widely scattered. Use this when your data is a small sample of a larger group.
Example: =STDEV.S(B2:B101) shows the variation or spread of the data in that column range.
COVARIANCE.S: Checks if two different sets of data tend to move in the same direction. For example, as temperature goes up, do ice cream sales also go up? Use this for sample data.
Example: =COVARIANCE.S(C2:C101;D2:D101) calculates the sample covariance to see if delivery time changes predictably alongside age.
COVARIANCE.P: Calculates covariance for an entire population. We do not use this on the exam. We always use COVARIANCE.S because course exercises are always based on sample data.
This function is for when you have data for every single member of a group, which is rare in practice. For exams, always use COVARIANCE.S.

COUNTIFS: Counts the number of cells that meet several rules at once. It only adds to the count if a cell satisfies all the conditions you set.

Simple Example: =COUNTIFS(A1:A10, "Yes", B1:B10, ">100") counts every row where column A says "Yes" AND the corresponding value in column B is over 100.

Visual Example: The formula looks for rows where column A is "Yes" and column B is greater than 100. Only rows 2 and 5 match both conditions.

	A	B	C
1	Attended?	Sales	Result
2	Yes	150	2
3	No	200	2
4	Yes	90	=COUNTIFS(A2:A6,"Yes",B2:B6,">100")
5	Yes	120
6	No	50

Absolute frequencies in binned data: =COUNTIFS($C$2:$C$101;">"&F2;$C$2:$C$101;"<="&G2) counts how many numbers in column C are strictly greater than the number in F2 AND less than or equal to the number in G2.

SUMPRODUCT: Imagine two shopping lists with items and their prices. This function multiplies the quantity of each item by its price and then adds up all those totals to give you the final bill.

Binned Data Mean: =SUMPRODUCT(F2:F7, G2:G7) calculates the mean for binned data, where F2:F7 contains the relative frequencies and G2:G7 contains the corresponding midpoints.

Visual Example: The formula multiplies each frequency by its midpoint (0.2*5, 0.5*15, etc.) and then sums those products to get the weighted mean.

	F	G	H
1	Rel. Freq	Midpoint	Mean
2	0.2	5	14.5
3	0.5	15
4	0.3	20
5			=SUMPRODUCT(F2:F4,G2:G4)

Binned Data Variance: =SUMPRODUCT(G2:G7;G2:G7;F2:F7) is used when finding the variance for binned data. It calculates the sum of (midpoint² * frequency). The full variance formula is typically (n/(n-1)) * (SUMPRODUCT_result - mean²).

Data Analysis Toolpak - Regression

A powerful tool that helps you understand how one factor (X) affects another (Y). It's used for least squares line and making predictions.
Setup Variables:
- Input Y range: The data you want to predict (the outcome).
- Input X range: The data you are using to make the prediction.
Output Interpretations: The toolpak provides a lot of information that we don't use in this course. The only outputs we need are:
- Multiple R: The correlation value. It tells you how strongly X and Y are related.
- R Square: Shows how much of the change in your outcome (Y) can be explained by your input factor (X). A higher percentage means a better fit.
- Observations: The total number of data rows evaluated (e.g., 100).
- Coefficients (Intercept): The value of a in the equation a+bx. This is your starting baseline when X is zero.
- Coefficients (Age/Variable X): The value of b in the equation a+bx. This is the slope, telling you how much Y changes for every single step change in X.