On 3 Nov 2005 14:57:19 -0800, "fsufan13" wrote:

Sorry, Ron, I didn't see your question. The output for the first cell
(M69) should be ------(blank) if the difference of par level (J69)
minus (E69) actual inventory divided by 2 < 0. This output is correct.
Then N69 should return ------ also. What these columns represent are
2 orders for the week, half on each of 2 days.

Does this make any sense? Thanks for your help.

Cheri


N69 is returning a #VALUE! error because one of its inputs, which is supposed
to be numeric, is your string of dashes. In your first formula, the
logical_test: (J69-E69)-M690 fails when it tries to subtract M69 from
(J69-E69). Since M69 is a text string ("-----") and since the formula requires
numeric entries, you get the error.

1. Your logical test is the same as J69-E69-M690. In this case, the
parentheses will not change Excel's order of evaluation. In your logical_test
formula, the addition and subtraction will be performed before the comparison
anyway.

2. To eliminate the VALUE error, you either have to have a "number" in M69; or
somehow test for it in your other formula
=IF((J69-E69)-M690,ROUND((J69-E69),0)-M69," ------- ")

To change the formula in M69, you could do:

=IF(J69-E690,ROUND(ROUND(J69-E69,0)/2,0),0)

but then custom format the cell to show the -'s. To custom format:
Format/Cells/Custom/Type: 0;0;*- will fill the cell with -'s if the value is
zero. You could also format a fixed number of -'s for zero; and you can format
the positive and negative numbers to show decimals.

If this is not convenient (perhaps you might normally want to have a zero in
that cell, then altering the other formula to check the contents of M69 before
doing the math:

=IF(ISNUMBER(M69),IF(J69-E69-M690,
ROUND((J69-E69),0)-M69," ------- "),"-------")


--ron