Forum Discussion
aayushman_mishra
Aug 17, 2019Copper Contributor
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,"")) |
- TwifooSilver Contributor
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,"")))- aayushman_mishraCopper Contributor
yes Twifoo , well thanks for resolving my issue.
- Haytham AmairahSilver Contributor
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
- aayushman_mishraCopper Contributor
thank you so much it worked out now.Haytham Amairah