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
mathetes's avatar
mathetes
Silver Contributor
Jun 09, 2020

amangar 

 

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.

  • amangar's avatar
    amangar
    Copper Contributor
    Jun 09, 2020

    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.

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

      amangar 

       

      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"))))

      • amangar's avatar
        amangar
        Copper Contributor
        Jun 09, 2020

        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!

Resources