Forum Discussion
IF function with calculation based on other cell
I am looking to use the IF function that produces a 1, 3, or 5 in E5 based on C1 based on the following criteria:
- if D7 is within 5 points of C7, then E7 should be 5
(i.e., 5 points greater than 60.8 or 5 points less than 60.8) - if D7 is within 10 points of C7, then E7 should be 3
(i.e., 10 points greater than 60.8 or 10 points less than 60.8) - if D7 is within 15 points of C7, then E7 should be 1
(i.e., 15 points greater than 60.8 or 15 points less than 60.8)
I'm not sure how to do this given that its a range of greater or less than data in one IF statement.
11 Replies
amangar I assume if it isn't within 15 it should be 0
I don't know why your IF statements aren't working or why you don't have IFS, but here is a formula without a single IF in it:
=MAX(5-2*INT((AVEDEV(D7,C7)*2)/5),0)EDIT: I just saw your attachment and see
a) your formulas are using <= not "within"
b) yes you wanted outside 15 to be 0
c) the if statement you typed worked fine for me (I re-typed the "=" to make excel recognize it)
Here is the changed formula to account for "within or equal to"
=MIN(MAX(2*INT((20-AVEDEV(D5,C5)*2)/5)-1,0),5)That is quite a formula!!
All because @amangar couldn't make IF work (and that caused by an invisible space in front of the IF function that really had nothing else wrong with it)
...necessity being the mother of invention, you came up with a doozy. Well done! Yet another illustration of how Excel gives many ways to get from point A to point B.
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.
