Want "$0", not "VALUE" to print

[STEVE]

I want to use a formula without a function so the word
"VALUE" doesn't print out. (Invoice)
I multiply 10 rows of 2 colums, say C and H.
C is quantity and H is price.
If there is no quantity ordered in C, I want $0 to print out,
NOT "VALUE"
Thanks!

 

[Gord Dibben]

If you are multiplying H1 by C1 your formula would be

=H1*C1

If either C1 or H1(or both)have no value the formula will return a zero, not an
error.

Show us the formula(s) you use.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

One more thought.

Your formula may be an array formula and you are not using CTRL + SHIFT + ENTER
to enter it.


Gord

 

[STEVE]

That's what I use.
The problem is when I then copy that formula by dragging it down the column,
the amount is the same all the way down, when they should be different
(price is different for each row)

This happens even though the formula in row 1 is =H1*C1
row 2 is =H2*C2
and C2 is a different price then C1.

 

[MartinW]

Hi Steve,

Just a guess here, because I don't fully understand your problem.

The #VALUE! error should only show in a multipication formula
if one of the values is text, are you maybe using an O instead of a zero.

Another possibility is that some cells are preformatted as text.
Try copying a blank cell, then selecting all of column H
then going Edit>Paste Special click 'add' and OK out.
Do the same for column C.

Does that change things for you?
Martin

 

[STEVE]

One of the values is a blank, other is currency. Use to give me blank as
result.
I tried your second suggestions, still get "VALUE".

 

[Gord Dibben]

Tools>Options>Calculation.....set to Automatic should update the results as you
copy down.

But, if you are getting the error it is because you have something other than
numbers or nothing in H and/or C

Also see Martin's reply to yoyr post.


Gord

 

[MartinW]

Hi Steve,

This all sounds very odd, one of Gord's or my suggestions should work.
If you like you can email the spreadsheet to (e-mail address removed)
and I will take a look at it.

If there is any private info on the spreadsheet that shouldn't be shared
over the net please delete those bits before sending the sheet.

Regards
Martin

 

[STEVE]

Martin,
Ok, it's coming to you.

Thanks for looking!
Steve