Forum Discussion

aayushman_mishra's avatar
aayushman_mishra
Copper Contributor
Aug 17, 2019

formula value error pls help

 I M EXTENDING THE FORMULA FOR MULTIPLE VALUES ITS SHOWING VALUE ERROR , PLEASE HELP ME REGARDING THIS, EXTENDED FORMULA IS MARKED GREEN.
I HAVE MULTIPLE 4 CONDITIONS THATS WHY I M EXTENDING THE FORMULA

the formula is to be applied in cell E6
,FILE ATTACHED

=IF(AND(B5-C5>=-200,B5-C5<=-1),B5-C5,IF(B5-C5=-750,-150,"")+IF(B5-C5=-650,-50,""))
  • Twifoo's avatar
    Twifoo
    Silver Contributor

    aayushman_mishra 

    Perhaps, this is the formula you need: 

    =IF(AND(B5-C5>=-200,B5-C5<=-1),B5-C5,
    IF(B5-C5=-750,-150,
    IF(B5-C5=-650,-50,"")))

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    aayushman_mishra

     

    Hi,

     

    This is the correct syntax of the formula:

    =IF(AND(B6-C6>=-200,B6-C6<=-1),B6-C6,IF(B6-C6=-750,-150,IF(B6-C6=-650,-50,"")))

    This syntax is called Nested IF.

     

    If you have Excel 2019 or Office 365, you can use IFS function instead which is simpler and more readable than Nested IF:

    =IFS(AND(B6-C6>=-200,B6-C6<=-1),B6-C6,B6-C6=-750,-150,B6-C6=-650,-50,TRUE,"")

     

    Regards

Resources