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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

STEVE - 14 Jan 2008 20:54 GMT

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!

Reply to this Message

Gord Dibben - 14 Jan 2008 21:40 GMT

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

>I want to use a formula without a function so the word
>"VALUE" doesn't print out. (Invoice)
[quoted text clipped - 3 lines]
>NOT "VALUE"
>Thanks!

Reply to this Message

Gord Dibben - 14 Jan 2008 21:44 GMT

One more thought.

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

Gord

>If you are multiplying H1 by C1 your formula would be
>
[quoted text clipped - 14 lines]
>>NOT "VALUE"
>>Thanks!

Reply to this Message

STEVE - 14 Jan 2008 22:45 GMT

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.

> One more thought.
>
[quoted text clipped - 21 lines]
> >>NOT "VALUE"
> >>Thanks!

Reply to this Message

MartinW - 14 Jan 2008 23:15 GMT

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

> That's what I use.
> The problem is when I then copy that formula by dragging it down the
[quoted text clipped - 37 lines]
>> >>NOT "VALUE"
>> >>Thanks!

Reply to this Message

STEVE - 14 Jan 2008 23:40 GMT

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".

> Hi Steve,
>
[quoted text clipped - 52 lines]
> >> >>NOT "VALUE"
> >> >>Thanks!

Reply to this Message

MartinW - 15 Jan 2008 00:52 GMT

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 woofared@hotmail.com
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

> One of the values is a blank, other is currency. Use to give me blank as
> result.
[quoted text clipped - 60 lines]
>> >> >>NOT "VALUE"
>> >> >>Thanks!

Reply to this Message

STEVE - 15 Jan 2008 02:52 GMT

Martin,
Ok, it's coming to you.

Thanks for looking!
Steve

> Hi Steve,
>
[quoted text clipped - 72 lines]
> >> >> >>NOT "VALUE"
> >> >> >>Thanks!

Reply to this Message

Gord Dibben - 15 Jan 2008 00:11 GMT

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

>That's what I use.
>The problem is when I then copy that formula by dragging it down the column,
[quoted text clipped - 30 lines]
>> >>NOT "VALUE"
>> >>Thanks!

Reply to this Message