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 FOR...
FORMULA ERROR
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Aug 17, 2019

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 https://support.office.com/en-us/article/if-function-%E2%80%93-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8.

 

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