This week, I looked at Excel again and the possibilities of working with data within it. I wanted to focus on the algebraic formula functions and what kinds of information these could lead to. I made a dummy dataset with two columns for “expenses” and “budgets” populated with random numbers. I created a third column using an IF statement to determine whether that specific case was over budget or ok. I used the formula =IF(A >B , “over budget”, “ok”) and also found that if you typed this in on cell with the corresponding numbers, you can then drag it down the column and it’ll automatically adjust the row numbers. Additionally, I calculated the success and failure rates of meeting the budget expectations by using =COUNTIF(C:C, “ok”)/COUNTA(C:C) for success and =COUNTIF(C:C,”over budget”)/COUNTA(C:C) for failure.
I think formulas like this would be a very interesting next level training because students often export data to R when they could be doing these calculations within Excel. Helping people get more comfortable with this sort of logic is also helpful for learning programming languages while being slightly more approachable.
I found that COUNTIF allowed you to calculate how many times a specific value showed up in a column, COUNTA calculated how many non-empty cells there were in a column, and COUNT gave you how many numeric values are in a column.
