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

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!

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

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE
wrote:

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!

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

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE
wrote:

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!

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

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.




"Gord Dibben" wrote:

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE
wrote:

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!

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

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 EditPaste Special click 'add' and OK out.
Do the same for column C.

Does that change things for you?
Martin


"STEVE" wrote in message
...
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.




"Gord Dibben" wrote:

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE

wrote:

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!

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

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


"MartinW" wrote:

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 EditPaste Special click 'add' and OK out.
Do the same for column C.

Does that change things for you?
Martin


"STEVE" wrote in message
...
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.




"Gord Dibben" wrote:

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE

wrote:

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!

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

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

On Mon, 14 Jan 2008 14:45:02 -0800, STEVE
wrote:

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.




"Gord Dibben" wrote:

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE
wrote:

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!

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

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
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" wrote in message
...
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".


"MartinW" wrote:

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 EditPaste Special click 'add' and OK out.
Do the same for column C.

Does that change things for you?
Martin


"STEVE" wrote in message
...
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.




"Gord Dibben" wrote:

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE

wrote:

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!

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

Martin,
Ok, it's coming to you.

Thanks for looking!
Steve

"MartinW" wrote:

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
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" wrote in message
...
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".


"MartinW" wrote:

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 EditPaste Special click 'add' and OK out.
Do the same for column C.

Does that change things for you?
Martin


"STEVE" wrote in message
...
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.




"Gord Dibben" wrote:

One more thought.

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


Gord

On Mon, 14 Jan 2008 13:40:29 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

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

On Mon, 14 Jan 2008 12:54:01 -0800, STEVE

wrote:

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!