# Excel Need help with Excel formula

Need help for an excel formula to perform the following:

Formula needed for Qty Missing

Qty Ordered - Qty Received = Qty Missing
10                      5                     5

Qty Ordered - Qty Received = Qty Missing
10              "if null add 0"           5

All Qty Missing cells should have a value.
###### Who is Participating?

Finance AnalystCommented:
Because column D has something other than blank, text value eventhough you can't see it.

Have you tried the IFERROR suggestion I gave earlier, it works on the sample you uploaded.

Thanks
Rob
0

How come 5

Qty Ordered - Qty Received = Qty Missing
10              "if null add 0"           5

Thanks
0

Author Commented:
Sorry:

Qty Ordered - Qty Received = Qty Missing
10              "if null add 0"        10
0

Assume qty ordered in cell A2=10 & qty received in cell B2=5 then formula in cell C2=A2-B2.

But I don't think you want this kind of simple answer.may be you are missing some thing in explanation.

Thanks
0

Or I guess you are very new to excel.  Mi right?
0

there is any instance when receiving qty exceed order qty?
0

Finance AnalystCommented:
Assume:

A2 = Order Qty

If B2 is blank, Excel will assume zero anyway.

Formula would be:
=A2-B2

10 - 0 = 10
10 - "blank" = 10

If B2 is a text value, even just an apostrophe, it will give error value. Maybe that is the issue here. The apostrophe could occur as a result of a download from another system or copy and paste of values from another formula where the zero option was set to "" rather than 0.

Workaround:

A2 = Order Qty

=IFERROR(A2-B2,A2)

This says, if the formula gives an error then use A2 (Order Qty) else work it out.

Thanks
Rob H
0

then try this in Cell C2
``````=IF((A2-B2)<0,"Qty Exceed "&ABS(A2-B2),A2-B2)
``````

See attached

Thanks
Qty.xlsx
0

As Per Mr.Rob H Approach & if you have multiple entries (which you want to total at the end) then try this version.

See Attached
Qty.xlsx
0

Author Commented:
The formulas above work on a new blank sheet.

Why do I get an error (#VALUE!) on my formatted Excel sheet?
All cells are general type.

Testing---Copy.xls
0

Author Commented:
The main problem was:
Because column D has something other than blank, text value even though you can't see it.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.