Mastering Excel’s IF Magic: A Step-By-Step Guide
I am on a journey to switch careers from education to data analytics. I have donned the moniker DataNewB, proudly declaring my newcomer status in the world of data.
Starting with Excel, I'm learning about conditional statements and formatting. My first task is to master the IF statement under the following objectives:
Objectives
1. Find the Salespersons who met the monthly goal using the IF statement.
2. Address the issue of Relative/Absolute reference that will arise.
3. Make use of Name Ranges in place of absolute reference.
I tried this function using the Sales Figures table above, curated by Kyle Pew in his Excel course on Udemy.
Columns A to F display the names of salespersons, their weekly sales, and the total figures each salesperson brought in within the 4-weeks in December 2020. Columns H2 to I2 reveal the monthly goal of $34,000, a figure each salesperson was tasked to meet before the end of the month.
What does the IF statement do?
The IF statement asks Excel to return specific values depending on whether a given condition is true or false.
To address the first objective, I entered the following formula into cell H5: =IF(F5>=I2, "YES", "NO").
This is to say, return the value YES if the total for Mr. H. James, the first salesperson ($36,245) is greater than or equal to the monthly goal ($34,000) and return the value NO if it isn't.
The formula returned a YES, which means Mr. H. James, the first salesperson met or surpassed the monthly goal
Great!
I used the awesome autofill feature in Excel to fill up the remaining cells in the range H5 to H9.
Oh, wait! There is an error.
The formula returned YES for the second salesperson who had a total sales of ($31,475), clearly below the monthly goal of ($34,000).
When I inspected cell H6 to find out why the formula returned a YES to the second salesperson, reaching the monthly goal instead of a NO. I noticed that my formula was using the default cell reference style in Excel, which is a relative reference.
The implication is that when I copied the formula to autofill the remaining cells, it adjusted to a new location. In this case, it adjusted to cell I3, an empty cell, instead of cell I2 where we have the monthly goal figure of ($34,000)
All I had to do was double-click on cell H5 and, in the formula, add a $ symbol before I and 2 respectively.
Something like this $I$2
This changed it from a relative to an absolute reference and made my reference to cell I2 remain constant, correcting the error experienced earlier as seen in the image below.
Another brilliant way to fix the issue of relative/ absolute referencing is to use Name Ranges. A Name Range is a way of renaming a cell or a range of cells.
In this case, I renamed the cell that contains the monthly goal figure. Instead of adding a $ symbol to make it an absolute reference, I moved my cursor to the name box at the far left, just before the formula bar. I made sure that the cell(s) highlighted in the name box is the cell(s) I want to rename.
I gave the name box a click, and changed the cell reference from I2 to "Goal".
With cell H5 selected, I moved my cursor to the formula bar and replaced $I$2 with the name "Goal".
When I double-click cell H5, you will notice that the cell reference changed from $I$2 to the new name range "Goal".
Isn't that awesome?
The beauty of using a name range is that I can be working on a different Excel sheet in the same workbook and let's say I want to reference the monthly goal figure, instead of moving back to check the cell reference, I can simply enter the name range and Excel will reference that cell.
That will be all on the IF statement and cell referencing styles.
Thanks for being a part of my journey.
