Forum Discussion

Elisabeth E's avatar
Elisabeth E
Copper Contributor
Nov 23, 2017

"IF" formula?

Hi, hoping someone can help me.

I am trying to make a sheet that reads analysis-results for me, i have different parameters within different intervals.
The result varies between different intervals - giving them a surtain "class".
F. eks, my result is 12.
The different classes are 1: <8, 2:  8-20, 3: 20-50, 4: 50-600 and 5: 600-1000.

(1: very good, 2: good, 3: moderate, 4: bad, 5: very bad)
Hence this is a class 2: good.

How do i write the formula so that the formatted cell tells me which interval my result is in? 
( it would be great to show the result not only in number, but maybe also with color? As the different classes are marked by colors.)

I have attatched my excel sheet.

excel if formula
Formulas & Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 23, 2017

    Hi Elisabeth,

     

    I'd suggest not to use strings to define the ranges but upper limits for them like

    When the formula which returns in which range the result is could be

    =IF(ISBLANK($B4),"No Data",INDEX($B$2:$F$2,0,MIN(IFNA(MATCH(H4,$B4:$F4,1),0)+1,5)))

    To color the class we may apply conditional formatting using similar formulas for the rules (one color - one rule) like

    =INDEX($B$1:$F$1,0,IFNA(MATCH(H4,$B4:$F4,1),0)+1)=2

    for green color.

    Sample is attached

Resources