Excel 2007 : Conditional formulas

Quick background information:
I'm trying to record some information about my boyfriend's video gaming, specifically how far he is in certain achievements. One column is how many points he has towards something, then the next couple columns are the different ranks of the achievement and how many it takes to meet the rank. ((See workbook.))

I have inserted a column that calculates how far I am to the next rank (Column "To Next Tier"). Right now, I have it done manually for each achievement, looking at the rank it's on and subtracting. But doing this 15-18 times each time he plays the game is overwhelming. (Can't just drag down a formula because different things are at different ranks.)



Now to the Question:
I want to know if there's a way to use formulas conditionally? Could I tell Excel to see "if C3 is less than E3 subtract from E3", but "if it's greater than E3 to subtract from F3" and so on?

Can the if-then function be used with formulas, or only formatting/text? It's the only thing I've found that seems like it could work, but all articles/tutorials online only mention text or color. If it can only be done manually, that's tolerable, I just would like to know of a lazier option if it's available.

Hi CaramelFishy and welcome to the forum,

If you have his points in a column and the goal in another column in the same row you could calculate the percent of goal with a simple dragdown operation.

It also sounds like you are wanting this http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx.

Hope this helps.

That's not quite it, sorry for the confusion.

Right now I have conditional formatting showing what rank it is (the cell with his current score is a different color for each rank). This time, I want a discreet number value, not a percentage.

What I need is a number that I can compare to other values (so he can say "I need 25 headshots to get to the next rank but only 15 grenade kills to it's next rank, so I'll work on grenades first.") There are different "goals" (multiple ranks), and that's what's giving me the trouble.

OK try the formula in the attached.

Hi,
My formula is based on http://www.business-spreadsheets.com/forum.asp?t=12

Please Login or Register  to view this content.

In words - I used the HLookup to find the value in the row less than the current level. Then I moved one cell to the right and subtracted his current level. I hope that helps you understand what I did. If you find "Evaluate Formula" and step through it, that might also help.