Text Causing a Problem in Formula

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

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
 
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))
 
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
 
Which formula did you use and what was in the cells that were used in the
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
 
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.)
 
Back
Top