Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
problem with a conditional max problem | Excel Discussion (Misc queries) |