Forum Discussion
IF function with calculation based on other cell
It might be in the most recent update.
If not, however, you can nest IF functions within one another.
=IF(condition1,result1,IF(condition2,result2,IF(condition3,result3,IF(condition4,result4,"NA"))))
mathetes I've tried various attempts of the nested formulas, and they don't seem to be working. I attached the document in hopes you could see the issue!
So far as I could tell, the only issue with your formulas was this: you had a leading space (invisible, but there) in front of the equals sign that begins the formula. So Excel was simply viewing it as text, not as a formula to be processed. Once I deleted that leading space the formulas worked.
That said, you'll see I created something else that I 'd recommend you think about. You are hard-coding some numbers into these formulas that could change. I refer to those allowed differences of 5, 10, and 15. I may be wrong, but those look like somewhat arbitrary numbers, and somebody could come along and say, "Why don't we allow for differences of 7.5, 15 and 25?"
If/when that were to happen you'd have to go into each of these individual formulas (not forgetting any places where they were hard-coded in) and change the values. There's a way to avoid that, highly recommended for this kind of analysis. That's to use named variables, based on a table somewhere in your workbook, such that you can then use the name in the formulas and, when a change is requested, you just change the values in the table and your formulas all automatically incorporate those new values in the results produced.
So you'll find a table in cells L3:M6 of the "Card" sheet, with the names "Tier1" through "Tier3" given to the values 5, 10, 15
I didn't change all of the formulas but did in a couple just so you could see how that would work. Here's an example of the formula:
=IF(ABS(D5-C5)<=TIer1,5,IF(ABS(D5-C5)<=Tier2,3,1))
That--the use of named ranges--is considered a good practice in Excel, for the reason given. It allows you to change variables that might change; it also allows you to easily explain why some lines show a "5", others "3" while others are "1" or "0"
mathetes I put the space on purpose when I was taking a screenshot to show the formula. I know that would stop the formula from working lol.
I ended up with the following formulas, but I wondered if they could be simpler. The criteria tab says what each formula should be based on for the scorecard.
You wrote: I put the space on purpose when I was taking a screenshot to show the formula. I know that would stop the formula from working lol.
There's a useful function: FORMULATEXT() that you could employ for that purpose. Put it somewhere else on the sheet and enter the cell reference in the brackets, and it will display the formula in the referenced cell, but leave it in place and functional.
You also wrote: I ended up with the following formulas, but I wondered if they could be simpler. The criteria tab says what each formula should be based on for the scorecard.
There's a "criteria tab"?? I couldn't find it. These are the tabs showing.
amangar - mathetes makes a great point but why not take it 1 step further and allow the awarded point (5,3,1) to also change? I used mathetes sheet and created a slightly bigger table and used a simple vlookup formula to find the correct award based on the difference. I had to play a trick with ROUNDUP and additional lines in the table to force the or equal to part to work. But now you can change either part real easy.
EDIT- Thanks mathetes for the kudos. I've done formulas like that a number of times rather than multiple if cases. They aren't too hard to come up with when you have simple patterns like steps of 5 the amount steps down by 2. the second formula made me step outside the box on how to go from < to <= and the answer was I 'inverted' everything. It was a 'fun' exercise. Sort of like this lookup table was a 'fun' exercise.
