Forum Discussion
IF function with calculation based on other cell
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"
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.