Presentation Transcript

1. Using Conditionals in Excel

2. Conditionals in Formulas There are plenty of nice ways to use conditionals in Excel formulas without actually programming For complex conditionals, a program is much easier to understand For simple conditionals, though, the Excel functions give us a great way to create nice spreadsheets
3. Example Workbook The Excel Workbook called Conditionals Example contains all the examples discussed in these slides It shows an imaginary Real Estate company’s spreadsheet, with sales prices for houses in various neighborhoods, and which agent sold them It has a number of added conditional features which we’ll discuss one by one
4. Basic Excel Functions The spreadsheet cells C18 and C19 show the average price of properties in the sheet, done in two different ways – take a look at the formulas (by clicking on the cell and looking at the formula window) But what if we want to know the average price for one salesperson?
5. Using SUMIF To find John M.’s total sales, we use the following formula for cell C20: =SUMIF(A7:A16, "John M.", C7:C16) This says to sum the elements of C7:C16 where the corresponding element of A7:A16 equals “John M.”
6. Using COUNTIF To get the average sale for John M., we need to divide by his number of sales. We can do this using COUNTIF in the formula for cell C21: =C20/COUNTIF(A7:A16, "John M.") COUNTIF(A7:A16, “John M.”) returns the number of entries in the given range that equal John M. Note they do have to be exactly the same
7. Sales per Neighborhood We can use the same idea to get average and total sales per neighborhood. This is done in C22 and C23 for Laurelhurst using the following formulas: For C22: =SUMIF(B7:B16,"Laurelhurst",C7:C16) For C23: =C22/COUNTIF(B7:B16,"Laurelhurst")
8. A More Complex Condition The most typical kind of more complex condition is where we want to have the AND of two or more conditions Excel has a built-in way to handle this using COUNTIFS and SUMIFS Let’s look at how to figure out John M.’s total and average sales in Laurelhurst That is, we want to count sales that are by John M. AND in Laurelhurst
9. Using COUNTIFS and SUMIFS John M.’s Laurelhurst Total (C24): =SUMIFS(C7:C16, A7:A16, "John M.", B7:B16, "Laurelhurst") Note the column to be summed comes first, followed by the other columns and their conditions. The comparison operator = is assumed. John M.’s Laurelhurst Average (C25): =C24/COUNTIFS(A7:A16,"John M.", B7:B16, "Laurelhurst")
10. An OR Condition Suppose we want a condition that is an OR instead of an AND, or uses a comparison other than =. One way to do it is to make a column for the condition and then use the column values in a SUMIF or COUNTIF For example, let’s say we want the total sales for houses in Irvington and Alameda. Logically, this means the Neighborhood field is Irvington OR it is Alameda
11. A New Column for the Condition Column E on the spreadsheet is the result of testing whether the neighborhood equals Irvington OR Alameda The formula looks like this: =IF(OR(B7="Alameda",B7="Irvington"),"yes","no") Note the format for the IF: IF(condition, true branch, false branch) These IF’s can be nested. But you can see that too much nesting would be hard to read
12. Using the Condition C26 has the total sales for properties in Irvington and Alameda Here is the formula, using Column E: =SUMIF(E7:E16,"yes",C7:C16)
13. Conditional Formatting Along with conditionals in formulas, Excel also lets you specify formatting using conditions There is a conditional formatting button in the main ribbon, shown on the next slide (We did this on the windows version but it is very similar on the Mac)
14. Data Bars Column C shows an example of Data Bars formatting The colored bar in each cell is determined by the cell value, with the smallest cell having only a small colored area and the largest one almost filled
15. Using Formulas Column A shows an example of using a formula in conditional formatting The formula must have a Boolean value In this case I colored the cell if the sale was in Laurelhurst and above the average price for Laurelhurst Here is the formula: =AND(C7>$C$23, B7="Laurelhurst")
16. If you make a lot of formatting rules… The Manage Rules item lets you edit and examine your rules
17. Try it Yourself! Knowing how to use conditionals makes Excel even more powerful You should play around with the example spreadsheet or one that you make yourself to understand the concepts