Forum Discussion

weareit12's avatar
weareit12
Copper Contributor
Nov 02, 2019

Logical Functions:

Can someone help me with writing the following function:

If X is =or > than 4.6 give it a value of 4 + if X is > 4.23 but <=to 4.60 give it a value of 3 + if X >3.85 but <= to 4.23 give it a value of 2 + if X < 3.85 give it a value of 1

Thanks

EXCEL DATA ANALYSIS
Excel if formula logical test problem
  • PReagan's avatar
    PReagan
    Bronze Contributor
    Nov 02, 2019

    weareit12 

     

    =IF(A1>=4.6,4,IF(A1>4.23,3,IF(A1>3.85,2,IF(A1<=3.85,1,"ERROR"))))

     

    It appears that you overlap some of your boundaries in your IF() statement so I am unsure if I guessed your boundaries correctly. Your boundaries state that if X>=4.6, then give a value of 4 and that if 4.6>=X>4.23, then give a value of 3. With these boundaries, you would see an error occur if X=4.6 because a value of 4.6 satisfies the conditions of a return value of both 4 and 3.

    Additionally, your IF() statement neglects a value of 3.85. Your boundaries return values if X>3.85 or X<3.85 but not if X=3.85.

Resources