|
PureBytes Links
Trading Reference Links
|
Colin:
>I'm trying to insert a cell location into a formula. Can't seem to work
>it out.
>
>I need to discover the cell location in which this is true:
>=MAX(COUNT(H3:H900)). And then use the value in that location as a
>divisor. Any ideas?
That formula doesn't make sense for two reasons:
1. If you have something in each cell in the whole range, then
COUNT(H3:H900) will always return 898. It'll do that even if
you have "" in the cells. You might try COUNTIF instead, as in
COUNTIF(H3:H900,">=0"). Then it will count all cells containing a
number >=0.
2. The formula =MAX(COUNT()) is redundant. The maximum value of the
return value from COUNT is simply COUNT.
If you want to find the location of the maximum value in a range,
that is, find the address of the cell in H3:H900 that equals
=MAX(H3:H900), then that's possible but not straightforward. You'd
first store the maximum value somewhere (say in C1), then build
another column comparing each H cell to the maximum, and wherever an
H cell equals the max, output the row number, e.g. in cell I3 goes
the formula: =IF(H3=$C$1,ROW(),0). Then =MAX(I3:I900) will contain
the row number of the maximum value in column H. You can use this
result with the ADDRESS function to generate an address of the cell,
e.g. =ADDRESS(MAX(I3:I900),COLUMN(H1),FALSE) would return "H374" if
cell H374 contains the maximum value in the H column.
--
,|___ Alex Matulich -- alex@xxxxxxxxxxxxxx
// +__> Director of Research and Development
// \
// __) Unicorn Research Corporation -- http://unicorn.us.com
|