Forum Discussion

Scott Cackett's avatar
Scott Cackett
Copper Contributor
Dec 11, 2017

Excel Formula help needed

Hi everyone,

I am trying to create a formula to help me increase/decrease insulin levels to help treat my diabetes.

Hopefully this makes sense. I need a formula to do the below:

 

If a number in cell e.g. A1 is between -2.0 and +2.0 then the cell to state "No Change"

If the number in cell A1 is lower than -2.0 but not lower than -4.0 (e.g. -3.5), then the cell should state "-0.1"

If the number in cell A1 is lower than -4.0 but not lower than -6.0 (e.g. 5.2), then the cell should state "-0.2"

If the number in cell A1 is lower than -6.0 the cell should state "Review!"

 

If the number in cell A1 is higher than 2.0 but not higher than 4.0 then the cell should state "+0.1"

If the number in cell A1 is higher than 4.0 but not higher than 6.0 then the cell should state "+0.2"

If the number in cell A1 is higher than 6.0 the cell should state "Review!"

 

I have tried the following (with H35 being the cell with the number in it) but when I try to add more to it, it says I've too many formulas.

 

=IF(H35>2,"+.1",IF(H35<-2,"-0.1",IF(H35<2,"No Change",IF(H35>-2,"No Change",IF(H35>4,"+.2")))))

 

Help!!!

Formulas & Functions
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 11, 2017

    Scott,

     

    What you need is something like this:

     

    =IF(AND(A1>=-2,A1<=2),"No Change",
  IF(AND(A1<-2,A1>=-4),"-0.1",
  IF(AND(A1<-4,A1>=-6),"-0.2",
  IF(AND(A1>2,A1<=4),"+0.1",
  IF(AND(A1>4,A1<=6),"+0.2",
  IF(OR(A1>6,A1<-6),"Review"))))))

     

    • Scott Cackett's avatar
      Scott Cackett
      Copper Contributor
      Dec 11, 2017

      =IF(AND(H35>=-1.9,H35<=1.9),"No Change",IF(AND(H35<=-2,H35>=-3.9),"-0.1",IF(AND(H35<=-4,H35>=-5.9),"-0.2",IF(AND(H35<=-6,H35>=-7.9),"-0.3",IF(AND(H35<=3.9,H35>=2),"+0.1",IF(AND(H35<=5.9,H35>=4),"+0.2",IF(AND(H35<=7.9,H35>=6),"+0.3",IF(OR(H35<=-7.9,H35>=8),"Review!"))))))))

       

      I've built upon the formula and am now trying to have it that if cell H35 states "0.0", that the cell will state "No Change"

       

      any ideas?

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Dec 13, 2017

        Just in case, alternative formula could be

        =LOOKUP(H35,
   {-9E+99,-7.89999,-5.9,-4,-1.99,2,4,6,8},
   {"Review!","-0.3","-0.2","-0.1","No Change","+0.1","+0.2","+0.3","Review!"}
)

        or like, depends on boundaries and precision.

         

Resources