Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
I have an equation in A1:
=if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
Best to treat it with an if/then
If you want it to be A1+10, then do =if(isblank(A1),10,A1+10) "Donny" wrote: I have an equation in A1: =if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
Why not just use
=10+IF(A1<"",A1,0) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Donny" wrote in message ups.com... I have an equation in A1: =if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
A1 can't be blank because it has a formla in it. Better to use:
=IF(A1="",10,A1+10) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Lisa" wrote in message ... Best to treat it with an if/then If you want it to be A1+10, then do =if(isblank(A1),10,A1+10) "Donny" wrote: I have an equation in A1: =if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
Try this:
=SUM(A1,10) "Donny" wrote: I have an equation in A1: =if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
One mo
=10+N(A1) Donny wrote: I have an equation in A1: =if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
"" + Number = #VALUE!
Are you sure that your "" doesn't get treated as zero in a plot the same way
that a zero is? In general, "" cells are treated as zeros, so to get the plot to ignore it the recommendation is to replace "" in your formula by NA(). -- David Biddulph "Donny" wrote in message ups.com... I have an equation in A1: =if(H1<45,"",H1) This is fine, if H1 is less than 45, then A1 stays blank. I don't want it to have a zero, because that would show up in a plot, it should be blank. But, in cell B1, I want the equation: =10+A1 This works fine if A1 has a value (<45), but it won't work if A1 has "" in it. I want: 10 + "" = 10 not: 10 + "" = #VALUE! Any suggestions without making "" be a zero? Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find/replace "cr" from end of number to "-" in front of number | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Formulae or format to change a number "1" into the word "one | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
How can I convert a Number into words. Ex: "10" as "Ten" | Excel Worksheet Functions |