asked on

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 Received doesn't always have a value - add 0 in Qty Received if null:
Qty Ordered - Qty Received = Qty Missing
10 "if null add 0" 5

All Qty Missing cells should have a value.

Naresh Patel

How come 5

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

Thanks

D J

ASKER

Sorry:

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

Naresh Patel

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

Naresh Patel

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

Naresh Patel

there is any instance when receiving qty exceed order qty?

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naresh Patel

then try this in Cell C2

=IF((A2-B2)<0,"Qty Exceed "&ABS(A2-B2),A2-B2)

Open in new window

See attached

Thanks
Qty.xlsx

SOLUTION

Naresh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

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.

Please see attached file.
Testing---Copy.xls

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

D J

ASKER

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

Thanks!