IF function with calculation based on other cell

amangar 

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"