# formula value error pls help

Occasional 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,""))
4 Replies
Highlighted

# Re: formula value error pls help

@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

Highlighted

# Re: formula value error pls help

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,"")))

Highlighted

# Re: formula value error pls help

thank you so much it worked out now.@Haytham Amairah

Highlighted

# Re: formula value error pls help

yes @Twifoo , well thanks for resolving my issue.