Spreadsheet Voodoo

I’m no wizard when it comes to spreadsheets but I feel I can hold my own when it comes to intermediate level functions. I learned one today that I can see being supremely useful in my nerd-laden future: If/Then functions!

Lets say you tutor for three different subjects: Chemistry, Calculus, and Physics. You aren’t any good at Chemistry so you only charge 5 bucks a session. You’re pretty decent at Calculus (so long as there aren’t any Riemann sums, infinite series or integration by partial fractions) so you charge 10 bucks for these sessions. Physics is your science of choice so you provide top notch service at a premium price of 15 dollars. You want your spreadsheet to look pretty so you format the cells in column A as drop down menus, each menu item being one of the subjects you tutor. Now wouldn’t it be nice if, when you chose the subject you tutored, the amount due was displayed to the right in column B? That’s where if/then statements come in handy. They look like this: IF(aaa=bbb,ccc) which reads: if a equals b, then c. You can add more conditions to it by separating them with commas and adding another IF statement. Ours would look like this:

IF(A2=”Chemistry”,5,IF(A2=”Calculus”,10,IF(A2=”Physics”,15)))

In our example it’s important to add the quotation marks to denote a string as opposed to a number. Also, strings are case sensitive so make sure you type the subject exactly as it appears in the drop down menu.

5 thoughts on “Spreadsheet Voodoo

  • Toni Hansen says:

    Do you just copy and paste it down column B, replacing A2 with A3, A4, etc? Or is there an easy way to say “just do it!!!”?

    • wetjosh says:

      Sorry for the late reply. I think you’re asking me about the “fill” feature. Click and drag the little white circle in the bottom right corner of the selected cell and it will do your copying for you. If you already knew that and were asking something else, please let me know.

      • Toni Hansen says:

        I did, but I had forgotten about it. Eventually I looked it up/remembered, but it wasn’t doing what I wanted because I needed the fill to go two different directions, if that makes sense, and it would only do one. Eventually I figured I was either asking too much of Numbers or I wouldn’t learn how to accomplish what I wanted to until lots of practice, which I wasn’t willing to put the effort into. So I broke it down into a bunch of tables and wrote the functions more spread out. I think it actually worked out better.

  • Toni Hansen says:

    posting to get notified if you reply

  • […] Naturally, I though I would just be able to use a simple sum function but that returns an error. In a previous post I outlined how to use an if/then statement. A similar approach is needed if we are to sum a column […]

Leave a Reply

Wordpress theme JaeDubya © 2018 | All Right Reserved | Designed & coded by J. Arthur Wetenkamp