Ignoring #VALUE! errors and MORE!!!!!
 

Using the following formula:
=IF(ISERROR((BB25-AB25)/2),"",BB25-AB25)/2

But it comes up with #value! errors (some data not available in each BB or
AB column)
I can hide these with conditional formatting but I am then using the
following formula linked to that column of cells:

=SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10 :$E$89="Female")($BE$10:$BE$89))/SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10: $E$89="Female"))

(The column witht the #VALUE! errors in is the BE10:BE89).

Any suggestions?? There must be a way of ignoring the error cells and just
extracting the data from the completed BE10:BE89 cells. OR do I have to do
something with BE10:BE89 cells in the first place to stop the #VALUE! errors
initially?

Many thanks you clever guys !!!!!

Ignoring #VALUE! errors and MORE!!!!!
 

Move the division by 2 into the False part of your formula...

=IF(ISERROR((B25-A25)/2),"",(BB25-AB25)/2)

--
Rick (MVP - Excel)


"Romileyrunner1" wrote in message
...
Using the following formula:
=IF(ISERROR((BB25-AB25)/2),"",BB25-AB25)/2

But it comes up with #value! errors (some data not available in each BB or
AB column)
I can hide these with conditional formatting but I am then using the
following formula linked to that column of cells:

=SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10 :$E$89="Female")($BE$10:$BE$89))/SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10: $E$89="Female"))

(The column witht the #VALUE! errors in is the BE10:BE89).

Any suggestions?? There must be a way of ignoring the error cells and just
extracting the data from the completed BE10:BE89 cells. OR do I have to do
something with BE10:BE89 cells in the first place to stop the #VALUE!
errors
initially?

Many thanks you clever guys !!!!!

Ignoring #VALUE! errors and MORE!!!!!
 

To explain... the #VALUE! error didn't come from the error produced by your
cells, it came from trying to divide text (in this case, the empty string ""
from your formula) by a number (in this case, the number 2).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Move the division by 2 into the False part of your formula...

=IF(ISERROR((B25-A25)/2),"",(BB25-AB25)/2)

--
Rick (MVP - Excel)


"Romileyrunner1" wrote in
message ...
Using the following formula:
=IF(ISERROR((BB25-AB25)/2),"",BB25-AB25)/2

But it comes up with #value! errors (some data not available in each BB
or
AB column)
I can hide these with conditional formatting but I am then using the
following formula linked to that column of cells:

=SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10 :$E$89="Female")($BE$10:$BE$89))/SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10: $E$89="Female"))

(The column witht the #VALUE! errors in is the BE10:BE89).

Any suggestions?? There must be a way of ignoring the error cells and
just
extracting the data from the completed BE10:BE89 cells. OR do I have to
do
something with BE10:BE89 cells in the first place to stop the #VALUE!
errors
initially?

Many thanks you clever guys !!!!!

Ignoring #VALUE! errors and MORE!!!!!
 

Brilliant!!! Works fine now.
Cheers Rick and blast my incompetence.

"Rick Rothstein" wrote:

To explain... the #VALUE! error didn't come from the error produced by your
cells, it came from trying to divide text (in this case, the empty string ""
from your formula) by a number (in this case, the number 2).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Move the division by 2 into the False part of your formula...

=IF(ISERROR((B25-A25)/2),"",(BB25-AB25)/2)

--
Rick (MVP - Excel)


"Romileyrunner1" wrote in
message ...
Using the following formula:
=IF(ISERROR((BB25-AB25)/2),"",BB25-AB25)/2

But it comes up with #value! errors (some data not available in each BB
or
AB column)
I can hide these with conditional formatting but I am then using the
following formula linked to that column of cells:

=SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10 :$E$89="Female")($BE$10:$BE$89))/SUMPRODUCT(($H$10:$H$89<2)*($I$10:$I$890)*($E$10: $E$89="Female"))

(The column witht the #VALUE! errors in is the BE10:BE89).

Any suggestions?? There must be a way of ignoring the error cells and
just
extracting the data from the completed BE10:BE89 cells. OR do I have to
do
something with BE10:BE89 cells in the first place to stop the #VALUE!
errors
initially?

Many thanks you clever guys !!!!!