Fun Functional Formulas

With the corporate world being so very much consumed by overwhelming amounts of data, programs like Excel become ever more popular. Not only are we pressured for time but also to remain accurate and concise in our work. Below is a list of some formulas we have found to increase the accuracy of our data and decrease the time spent in front of the computer.

Now, if you’re inputting tons of data into Excel but aren’t familiar enough with the program, then the formula ribbon will probably look like Chinese to you. That’s okay, read along to gain some insight on some helpful tools.

1. COUNT

This basic formula will count any cells with numbers within the range you select. Simple enough, right? Good let’s keep going.

2. IF

“IF” formulas will perform a logical test to see if a condition is met within a select range of cells and returns a value if True or False (we will see these words a lot more in a minute). Meaning you set the rules for what to test for and what to say if the cells pass the test, True, or if it fails the test, False. Make sense? If not here is an example.

Let’s say your boss owns some commercial office buildings. He wants you to review previous years of revenue that was generated from leasing agent’s sales. He wants to compare the data of the goals he set for his agents and verify they are obtainable goals for them to make a bonus on their paycheck.

The table below shows one year broken down to sales each month for one of his agents. Straightforward so far?

In column E the formula “IF” was used to show if this agent met her quota that month and “Hit Bonus” (True) or if she “Did Not Meet” the goal thus not making a bonus, (False). In this case value is any text you want, not necessarily a number as the word suggests; it is just text that is returned if the cells test True or False to any test you set. The test that will be used is: the values in column C (Gross Revenue) have to be of greater value than the cells in column D (Owner’s Goal). Typed into cell E2 is the following:

=IF(C2>D2, “Hit Bonus”, “Did Not Meet”)

This means that if the leasing agent sold more than $15,000 of total revenue that month they met their goal and hit bonus. If they sold less than $15,000 then they did not meet their goal to hit bonus set forth by the owner. This formula will be repeated for each month. The next formula will have C3>D3 so forth and so on.

3. COUNTIF

The hard part is over!! Okay, so now that you understand “IF” and “COUNT” formulas you can combine them to count the cells that meet a set of criteria. In this case the criteria are simply just the previously labeled value. Use COUNTIF when you need to count how many cells have numbers that meet a certain criteria in the range you set. The range is just that, the range of cells you want to test. You want to test all the cells from E2 to E13.

You need to count how many times each agent “Hit Bonus” (criteria). And just because we have a needy boss we add the Average Revenue if they hit bonus and the Sum of all the revenue generated when the hit bonus.

=COUNTIF(E2:E13, “hit bonus”)

=AVERAGEIF(E2:E13, “hit bonus”, C2:C13)

=SUMIF(E2:E13, “hit bonus”, C2:C14)

This will tell you that this agent hit bonus two times. The formula is counting any cells between E2 to E13 that have the text “Hit Bonus”. In Excel, once you understand all the induvial basic formula’s it will be a cake walk from here on out!  Are you ready for some cake?