Count Common Denominator, but excluding "#value!"
 

i want to count the (common denominator in a cell range a.k.a (which number
appears the most")


in F6 to F200 contain formulas as described below giving me a value from
another cell. ex" #4"
ex: (=e6) which gives me "4" from "E6"
(=e7 ) which gives me "5" from "E7"
(=e8) wich gives me "5" from "e8"
to row 200

Some cells "give me "#value!"

In G1 i tried this formula but it does not work since it cant count the
cells with "values"

=mode(F6:F200)

How can i use the same formula but also to ignore "value" errors and to
finally give me the comon denominator. Which from the above examle number 5
appears the most.

Count Common Denominator, but excluding "#value!"
 

Hi,

Try this
=MODE(IF(ISERROR(F6:F200),FALSE,F6:F200))

It's an array so enter with Ctrl+Shift+enter
Excel will put curly brackets around the formula.

Mike

"Jman" wrote:

i want to count the (common denominator in a cell range a.k.a (which number
appears the most")


in F6 to F200 contain formulas as described below giving me a value from
another cell. ex" #4"
ex: (=e6) which gives me "4" from "E6"
(=e7 ) which gives me "5" from "E7"
(=e8) wich gives me "5" from "e8"
to row 200

Some cells "give me "#value!"

In G1 i tried this formula but it does not work since it cant count the
cells with "values"

=mode(F6:F200)

How can i use the same formula but also to ignore "value" errors and to
finally give me the comon denominator. Which from the above examle number 5
appears the most.

Count Common Denominator, but excluding "#value!"
 

Just a heads-up on that formula...

It will evaluate empty cells as numeric 0 and could return 0 as the mode.

Probably better to use (array entered):

=MODE(IF(ISNUMBER(F6:F200),F6:F200))



--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

Try this
=MODE(IF(ISERROR(F6:F200),FALSE,F6:F200))

It's an array so enter with Ctrl+Shift+enter
Excel will put curly brackets around the formula.

Mike

"Jman" wrote:

i want to count the (common denominator in a cell range a.k.a (which
number
appears the most")


in F6 to F200 contain formulas as described below giving me a value from
another cell. ex" #4"
ex: (=e6) which gives me "4" from "E6"
(=e7 ) which gives me "5" from "E7"
(=e8) wich gives me "5" from "e8"
to row 200

Some cells "give me "#value!"

In G1 i tried this formula but it does not work since it cant count the
cells with "values"

=mode(F6:F200)

How can i use the same formula but also to ignore "value" errors and to
finally give me the comon denominator. Which from the above examle
number 5
appears the most.

Count Common Denominator, but excluding "#value!"
 

I tried both formulas, and i am still getting a "#value!" :(

"T. Valko" wrote:

Just a heads-up on that formula...

It will evaluate empty cells as numeric 0 and could return 0 as the mode.

Probably better to use (array entered):

=MODE(IF(ISNUMBER(F6:F200),F6:F200))



--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

Try this
=MODE(IF(ISERROR(F6:F200),FALSE,F6:F200))

It's an array so enter with Ctrl+Shift+enter
Excel will put curly brackets around the formula.

Mike

"Jman" wrote:

i want to count the (common denominator in a cell range a.k.a (which
number
appears the most")


in F6 to F200 contain formulas as described below giving me a value from
another cell. ex" #4"
ex: (=e6) which gives me "4" from "E6"
(=e7 ) which gives me "5" from "E7"
(=e8) wich gives me "5" from "e8"
to row 200

Some cells "give me "#value!"

In G1 i tried this formula but it does not work since it cant count the
cells with "values"

=mode(F6:F200)

How can i use the same formula but also to ignore "value" errors and to
finally give me the comon denominator. Which from the above examle
number 5
appears the most.

Count Common Denominator, but excluding "#value!"
 

never mind it was a typo on my part. It worked..
Thanks Guys.

"T. Valko" wrote:

Just a heads-up on that formula...

It will evaluate empty cells as numeric 0 and could return 0 as the mode.

Probably better to use (array entered):

=MODE(IF(ISNUMBER(F6:F200),F6:F200))



--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Hi,

Try this
=MODE(IF(ISERROR(F6:F200),FALSE,F6:F200))

It's an array so enter with Ctrl+Shift+enter
Excel will put curly brackets around the formula.

Mike

"Jman" wrote:

i want to count the (common denominator in a cell range a.k.a (which
number
appears the most")


in F6 to F200 contain formulas as described below giving me a value from
another cell. ex" #4"
ex: (=e6) which gives me "4" from "E6"
(=e7 ) which gives me "5" from "E7"
(=e8) wich gives me "5" from "e8"
to row 200

Some cells "give me "#value!"

In G1 i tried this formula but it does not work since it cant count the
cells with "values"

=mode(F6:F200)

How can i use the same formula but also to ignore "value" errors and to
finally give me the comon denominator. Which from the above examle
number 5
appears the most.