Forum Discussion

amangar's avatar
amangar
Copper Contributor
Jun 09, 2020

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...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Jun 09, 2020

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)

 

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jun 09, 2020

    mtarler 

    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.

Resources