Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to
remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
Hi
It sounds as though you have a space character in A1 or B1 or Both. Do you have a formula in them which is set to return a Null if false? If so, this formula may be incorrect. For example in A1 =IF(D5=50,5,"") may have been wrongly entered as =IF(D5=50,5," ") You could amend your formula to =IF(COUNT(A1:B1)<2,"",B1/A1) which will work whether there are spaces or nulls in A1 and B1 -- Regards Roger Govier "novice" wrote in message ... I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
"I would like c1 to display b1/a1"
=IF(AND(A1="",B1=""),"", B1/A1) when b1=3, a1=5 1. do you want to literally display as "3/5" or 2. do you want to display the result value as 0.6. or when b1 is blank, a1=5 (vice versa) 3. do you want to literally display as "/5" or 2. do you want to display the result as blank"". this counter question is for philosophical understanding of your need. "novice" wrote: I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
I would like option 2 for a result in both cases
"driller" wrote: "I would like c1 to display b1/a1" =IF(AND(A1="",B1=""),"", B1/A1) when b1=3, a1=5 1. do you want to literally display as "3/5" or 2. do you want to display the result value as 0.6. or when b1 is blank, a1=5 (vice versa) 3. do you want to literally display as "/5" or 2. do you want to display the result as blank"". this counter question is for philosophical understanding of your need. "novice" wrote: I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
Hi, this worked as long as I put in a value larger than 0. In this case I
changed your example to IF(COUNT(A1:B1)<0.01,"",B1/A1) so I could use it for basically all values. However, if I write IF(COUNT(A1:B1)<0,"",B1/A1) I still get #VALUE! "Roger Govier" wrote: Hi It sounds as though you have a space character in A1 or B1 or Both. Do you have a formula in them which is set to return a Null if false? If so, this formula may be incorrect. For example in A1 =IF(D5=50,5,"") may have been wrongly entered as =IF(D5=50,5," ") You could amend your formula to =IF(COUNT(A1:B1)<2,"",B1/A1) which will work whether there are spaces or nulls in A1 and B1 -- Regards Roger Govier "novice" wrote in message ... I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
hi novice,
it seems your question is already served by a checked... you can post another ?thread for another topic.. happy holidays...hohoho "novice" wrote: I would like option 2 for a result in both cases "driller" wrote: "I would like c1 to display b1/a1" =IF(AND(A1="",B1=""),"", B1/A1) when b1=3, a1=5 1. do you want to literally display as "3/5" or 2. do you want to display the result value as 0.6. or when b1 is blank, a1=5 (vice versa) 3. do you want to literally display as "/5" or 2. do you want to display the result as blank"". this counter question is for philosophical understanding of your need. "novice" wrote: I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
problems with if function
Hi
That will never work. The count is being used to ensure that there are numeric values in A1 and B1. If there are, Count will be 2 and the calculation B1/A1 will work. If the count is less than 2, then either or both cells do not contain numeric values, and the cell will show a null. Use the formula exactly as I posted. =IF(COUNT(A1:B1)<2,"",B1/A1) -- Regards Roger Govier "novice" wrote in message ... Hi, this worked as long as I put in a value larger than 0. In this case I changed your example to IF(COUNT(A1:B1)<0.01,"",B1/A1) so I could use it for basically all values. However, if I write IF(COUNT(A1:B1)<0,"",B1/A1) I still get #VALUE! "Roger Govier" wrote: Hi It sounds as though you have a space character in A1 or B1 or Both. Do you have a formula in them which is set to return a Null if false? If so, this formula may be incorrect. For example in A1 =IF(D5=50,5,"") may have been wrongly entered as =IF(D5=50,5," ") You could amend your formula to =IF(COUNT(A1:B1)<2,"",B1/A1) which will work whether there are spaces or nulls in A1 and B1 -- Regards Roger Govier "novice" wrote in message ... I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to remain empty if nothing in a1 and b1 the following works but only if there is a value in a1 and b1. =IF(AND(A1="", B1=""),"", B1/A1) if a1 and b1 empty c1 shows hashkey value! what am I doing wrong |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems updating shared documents which used "Lookup" function | Excel Discussion (Misc queries) | |||
numerical integration | Excel Discussion (Misc queries) | |||
Linest function: data selection problems | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
opening up an english excel sheet onto a french verions - problems with edate function | Excel Discussion (Misc queries) |