Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
I copy data from a web page and paste it into a table. Then in other
tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
Maybe you can check for numbers first:
=if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
Hi,
I tried both of the formulas you suggested, but it doesn't eliminate the #value! error. Is the second formula supposed to have an "n" in it? Thanks On Sun, 16 Dec 2007 09:24:30 -0600, Dave Peterson wrote: Maybe you can check for numbers first: =if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
Yes.
Are you sure that B4 has a numeric value in it? Peter wrote: Hi, I tried both of the formulas you suggested, but it doesn't eliminate the #value! error. Is the second formula supposed to have an "n" in it? Thanks On Sun, 16 Dec 2007 09:24:30 -0600, Dave Peterson wrote: Maybe you can check for numbers first: =if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
Yes.
On Sun, 16 Dec 2007 12:07:14 -0600, Dave Peterson wrote: Yes. Are you sure that B4 has a numeric value in it? Peter wrote: Hi, I tried both of the formulas you suggested, but it doesn't eliminate the #value! error. Is the second formula supposed to have an "n" in it? Thanks On Sun, 16 Dec 2007 09:24:30 -0600, Dave Peterson wrote: Maybe you can check for numbers first: =if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
Which formula did you use and what was in the cells that were used in the
formula? Peter wrote: Yes. On Sun, 16 Dec 2007 12:07:14 -0600, Dave Peterson wrote: Yes. Are you sure that B4 has a numeric value in it? Peter wrote: Hi, I tried both of the formulas you suggested, but it doesn't eliminate the #value! error. Is the second formula supposed to have an "n" in it? Thanks On Sun, 16 Dec 2007 09:24:30 -0600, Dave Peterson wrote: Maybe you can check for numbers first: =if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
Dave, you've never steered me wrong so I double checked with the
"isnumber" formula. After much tweaking and playing around I finally got it to work. I can't believe I figured it out, but I did. The formula has to change from cell to cell within the range, but it works. =IF(B3="","",IF(ISNUMBER(F3),(0.5*(B3*($F$1+1)*3)-1))) It looks like this now and does the job. Thanks again for all of your help Peter On Mon, 17 Dec 2007 08:53:15 -0600, Dave Peterson wrote: Which formula did you use and what was in the cells that were used in the formula? Peter wrote: Yes. On Sun, 16 Dec 2007 12:07:14 -0600, Dave Peterson wrote: Yes. Are you sure that B4 has a numeric value in it? Peter wrote: Hi, I tried both of the formulas you suggested, but it doesn't eliminate the #value! error. Is the second formula supposed to have an "n" in it? Thanks On Sun, 16 Dec 2007 09:24:30 -0600, Dave Peterson wrote: Maybe you can check for numbers first: =if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text Causing a Problem in Formula
It doesn't look like the check for isnumeric(f3) does anything in your formula.
You don't use F3 in any arithmetic operation. Try putting 1 in B3 and "asdf" in F3. (I think you have some more work to do.) Peter wrote: Dave, you've never steered me wrong so I double checked with the "isnumber" formula. After much tweaking and playing around I finally got it to work. I can't believe I figured it out, but I did. The formula has to change from cell to cell within the range, but it works. =IF(B3="","",IF(ISNUMBER(F3),(0.5*(B3*($F$1+1)*3)-1))) It looks like this now and does the job. Thanks again for all of your help Peter On Mon, 17 Dec 2007 08:53:15 -0600, Dave Peterson wrote: Which formula did you use and what was in the cells that were used in the formula? Peter wrote: Yes. On Sun, 16 Dec 2007 12:07:14 -0600, Dave Peterson wrote: Yes. Are you sure that B4 has a numeric value in it? Peter wrote: Hi, I tried both of the formulas you suggested, but it doesn't eliminate the #value! error. Is the second formula supposed to have an "n" in it? Thanks On Sun, 16 Dec 2007 09:24:30 -0600, Dave Peterson wrote: Maybe you can check for numbers first: =if(isnumber($f$1),(0.5*(B4*($F$1+1)*3)-1),"") or use 0 if you find text: =IF($F$1="","",(0.5*(B4*(n($F$1)+1)*3)-1)) Peter wrote: I copy data from a web page and paste it into a table. Then in other tables I do some calculations. For example I have this formula: =IF($F$1="","",(0.5*(B4*($F$1+1)*3)-1)) The problem is that cell F1 refers to a text string that looks like three tiny dashes - - - Now, in my new table I get the value error. I need to get rid of this value error so I can do other calculations. One of which is to rank some columns but that won't work with the #VALUE! in there. Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
= Text formula problem | New Users to Excel | |||
Find and Replace causing a "Text" cell to become a custom date--wh | Excel Discussion (Misc queries) | |||
VLOOKUP Formula causing an error | Excel Discussion (Misc queries) | |||
Formula Causing a Save Error Message | Excel Worksheet Functions | |||
minus numbers causing a problem | New Users to Excel |