Text Causing a Problem in Formula

[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

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

Yes.

Are you sure that B4 has a numeric value in it?

 

[Peter]

Yes.

Yes.

Are you sure that B4 has a numeric value in it?

 

[Dave Peterson]

Which formula did you use and what was in the cells that were used in the
formula?

 

[Peter]

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

 

[Dave Peterson]

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.)