big problem
 

There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.

big problem
 

Hi,

I don't understand this. If A2 & B2 are empty your formula will show 0
(Zero) the only way it will show a #value! error is if there are text entries
in these cells. How do data get into A2 and B2? are they formulas?

Mike

"Wu" wrote:

There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.

big problem
 

Hi Wu,

Try this =IF(OR(A2="",B2=""),"",A2*B2)
--
Regards
Warren
Excel Novice

If this helps please click the Yes button.


"Wu" wrote:

There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.

big problem
 

You don't get #VALUE! if there is no data, but if there is text instead of a
number.
You may want to try =IF(COUNT(A2:B2)=2,A2*B2,"")
--
David Biddulph

"Wu" wrote in message
...
There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.

big problem
 

MIKE H, You are right, actually, I have set formulas into A2 and B2,

In A2 , ='sheet2'!F2 , THAT MEANS THE SOURCR OF A2 IF FROM F2 OF SHEET2
In B2 , ='sheet2'!G2

"Mike H" wrote:

Hi,

I don't understand this. If A2 & B2 are empty your formula will show 0
(Zero) the only way it will show a #value! error is if there are text entries
in these cells. How do data get into A2 and B2? are they formulas?

Mike

"Wu" wrote:

There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.

big problem
 

Another one:

=N(A1)*N(B1)

This will show 0 if either are text.

Wu wrote:

There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.

--

Dave Peterson

big problem
 

Sorry if this double-posts, but there seems to be problems today and I can't
see the post I thought I made.

The problem probably is that F2 or G2 contain spacebars, hidden characters,
or formulas that evaluate to "" or " ".

To clear up spacebars and hidden characters, select the cells and press Del.
To deal with the "" or " " change your formulas that read
='sheet2'!G2
to
=IF(sheet2!G2="",0,sheet2!G2)
If there is any other text in G2 then you might do
=IF(istext(sheet2!G2),0,sheet2!G2)
this second solution will work for the "" problem also.

cheers,
Shane

"Mike H" wrote in message
...
Hi,

I don't understand this. If A2 & B2 are empty your formula will show 0
(Zero) the only way it will show a #value! error is if there are text
entries
in these cells. How do data get into A2 and B2? are they formulas?

Mike

"Wu" wrote:

There are three column contain "Quantity" (A1), "Unit price" (B1) and
"Amount" (C1)

I have set a formula at "Amount" (C2)that =A2*B2, it is work , but if
anyone
of A2 or B2 is no data, or both of them are no data, C2 will show
#value!

How can it does not show #value! even there are no data at A2 and B2.