#VALUE! and #N/A errors

hi all i need help on the following

In cells BS8 I have the following =100*SUBSTITUTE(BR16,"metres","") to get centremetres from cell BR8 and in cell BT8 I have the following

=IF(BS8,ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0),"") I get a #Value error

I have also tried
=ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0)
In BT8 but I get same error

I have used it in another sheetsheet and it works I don’t understand why is is happening

Plus is there a way to get rid of #N/A as I am using the following in another cell on same spreadsheet
=VLOOKUP(F8,'centre No'!C:D,2,0)
it works but I don’t want the #N/A showing

Hi Duckie
The #Value means data of wrong type in calculation
and =IF(BS8,ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0),"") one of these is text not a number

the second part ...with 2007 use
=IFERROR(VLOOKUP(F8,'centre No'!C:D,2,0),0 )
will return Zero for errors

hmmm..... can you attach a workbook?

Hey Duckie
use the IFERROR() again
=IFERROR(100*SUBSTITUTE(BQ8,"metres",""),0)
this is happen when BQ8 is empty

your welcome

Hi Duckie
Whats the formula? the sheet I have dosent have a formula in total points
or can you use the Iferror again?

As outlined already by Pike the #VALUE is because of the explicit coercion taking place via addition where some values are "non-numeric" by nature, eg:

=BY8+CA8 --> #VALUE! because a Null ("") is in effect a 0 len non-numeric text string which can not be coerced to number

=SUM(BY8,CA8) --> 1100 ... SUM does not explicitly coerce in this context thus the Null in CA8 is simply ignored.

However, rather than having

=SUM(K8,M8,...CA8)

Have you considered using SUMIF ?

=SUMIF($K$6:$CA$6,"points",$K8:$C8)

like SUM it won't explicitly coerce but it's a bit simpler to setup I'd say.