I have been learning the various functions available in excel, through creating a worksheet for Restaurant City, a Facebook game that I’m currently playing.

The object of the game is to run your own restaurant, and level up dishes by collecting ingredients. There’s a total of 119 dishes (new dishes are released weekly) and 62 ingredients. So I came up with formulas to keep track of the dishes and ingredients, and calculate the ingredients to buy or trade.

I started working on this sheet in November, making use of various excel functions, some of which I never even knew of, until I was thinking up of more efficient ways to make my formulas do what I needed them to accomplish.

Excel functions used:

• IF, AND, SUM, ABS, ROUNDDOWN, MIN – These functions I already knew of. Thinking up of the formulas to make them work properly was still challenging.
• CONCATENATE – I stole this function off of my co-worker’s quote sheet. I needed a way to display text from 2 different cells, and his quote sheet did exactly just that, so I copied it.
• COUNTIF – I came across this function while searching for a more efficient way to count how many dishes I was currently working on, and how many are already at level 10. I used to just have an IF flag that changes to 1 or 0, depending on the level of the dish, and I would just use SUM to add up all the flags. COUNTIF eliminated the 2 extra columns that I was using for the flags.
• VLOOKUP – This function was suggested by one of the user’s of my RC sheet. It took a while for me to figure out how it worked, but once I figured it out, it eliminated the extra 127 columns I was using to match the ingredients to the dishes. It’s so efficient that it cuts the time that I spend updating the sheet (to add new dishes) by 85%.
• SUMIF – When VLOOKUP wasn’t working the way that I wanted it to, I had to search for a different function that will lookup and add multiple values. This proved to be just the function that I was looking for. I thought I had to go to the old way of matching the ingredients to the dishes and add the extra 127 columns again, but thankfully, I didn’t have to.

Other Excel features used:

• Hiding columns, rows, sheets, and locking cells – I hid extra formulas that don’t really need to be seen, and locked the cells that don’t need to be modified. This makes the sheet foolproof and the formulas won’t be accidentally deleted.
• Data Validation – Just another step to make the sheet foolproof. So that users won’t enter “apple” when it’s supposed to just be a number in that cell
• Conditional Formatting – Automatically changing colors that act as flags and lets you know if a dish is a certain level, or if you have enough ingredients on hand.
• Cell and Range Naming – Again, this was suggested by one of the user’s of my RC sheet. I didn’t know that you can name cells and ranges, and this proved to be quite useful. Instead of trying to figure out what cell Apple is in, I can just call it with =Apple. No need to keep a reference sheet with all the cell numbers anymore.

Click here to download RC Excel Worksheet Version 2.10
(Updated March 30, 2010)