Forum Discussion
IF function with calculation based on other cell
Let me explain the answer briefly.
First, the syntax of IFS is as follows =IFS(condition1,result1,condition2,result2,condition3,result3,....etc)
It stops once it reaches a condition that is satisfied.
I initially wrote it without the ABS, which gets the absolute difference between the two numbers, no matter which is the larger. Whether C7 is larger or smaller than D7 by those prescribed conditional limits, this will produce your desired results.
mathetes I don't have the IFS function for my Excel. It looks like it doesn't apply to the MS Office package I bought.
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"