How to avoid #value! error

[cndu]

This is my equation, i'm unable to complete it

=IF(X2="CANCELLED", 0, IFERROR(AA2*AJ2, 0

[Glenn Kennedy]

Can you expalin what you want it to do?

[cndu]

Can you expalin what you want it to do?

Hi,

I have to add up Value Added Tax (VAT) with the Order Cost for all the orders which have been processed. For orders cancelled, this formula should return a value of 0. The VAT is zero for Orders which are not managed by me (offline orders). So when I use the above formula, for all the blank VAT cells, the value returns as #Value! which I want to replace with 0 or a blank.

please help.

[JohnTopley]

It would help if you can post a file showing the expected outcome(s). The formula you posted is incomplete.

[cndu]

It would help if you can post a file showing the expected outcome(s). The formula you posted is incomplete.

Please find the file attached. I'm refering to AG2

[sanju2323]

try This formulas

=IFERROR(Your Formula),"")

[Shareez Saleem]

EF.png
It looks perfect, no error messages shows in your list.

Use any of the below formula:

=IF(X2="CANCELLED","",AA2*AJ2)
=IFERROR(IF(X2="CANCELLED",0, AA2*AJ2,""))

[sanju2323]

Shareez Saleem, =IFERROR(IF(X2="CANCELLED",0, AA2*AJ2,"")) this formula is incorrect
Right answer is =IFERROR(IF(X2="CANCELLED", 0, AA2*AJ2),"")

[Shareez Saleem]

Sorry, it was a typo error.

Thanks Sanju.

[sanju2323]

cndu,
Is the wrong from your original request.I would suggest to mark "solved" this thread, accepting the solution, if you think that your original request was answered, and start a new thread with your last request. It's also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

[shukla.ankur281190]

Why you are getting #value error?As per checking your sheet it seems all the If function giving you a numeric value and #value error only came if any numeric will involve any math operation with text.

Your formula is perfectly okay in the mean time, however if you wanna avoid any error like #vaue,#N/A and all that you can simple use Iferror formula. As suggest by Mr.Sanju in #5.

[cndu]

In that excel sheet.. Can anyone tell me dhow to derive the Profit