Excel 2007 : Convert a COUNT number to a cell reference

[egavasrg]

Hi All:

I have Excel 2007. I am using COUNT to identify 2 cell locations/positions in a list. In the attached spreadsheet, Formula A returns the number 13, Formula B returns the number 14. The actual cells (which would be A14 and A15) contain the values 59 and 97 respectively. Once I know the cell positions, I would like to attach the column letter (A, B, C, etc.) to the number returned by the count so I can do additional math with the actual cell values.

As you can see in Formula A, I get A13 but it is not a cell reference. In Formula B, when I try to use the VALUE and "&" functions to create a cell reference I get a #VALUE! error. Is there a way to convert a cell position derived from a COUNT operation to a cell reference so I can use the cell contents in calculations?

Ultimately, I'm trying to create a formula to manually calculate PERCENTILE to correct for the difference in PERCENTILE calculations between Excel 2007 and Excel 2010. For more information on that difference,
please see http://www.youtube.com/watch?v=13Ahbn9o-5I.

[Marcol]

Your formula

=ROUNDDOWN(SUM(COUNT(A2:A15)+1)*E2,0)

Returns 13 when E2 = 0.9

Therefore

=INDIRECT("A"&ROUNDDOWN(SUM(COUNT(A2:A15)+1)*E2,0))

Returns 54 i.e. the value in Column "A" Row 13
and

=INDIRECT("A"&ROUNDDOWN(SUM(COUNT(A2:A15)+2)*E2,0))

Returns 59

Is this what you are after?

[egavasrg]

:Marco:

Thanks for the quick and accurate response. This completely meets my needs. You are fantastic!

[martindwilson]

you only need index
=INDEX(A:A,ROUNDDOWN(SUM(COUNT(A2:A15)+1)*Percent,0)) gives the value in a13

[egavasrg]

Thanks! This is very useful. I also appreciate the additional reading.

[Marcol]

Agreed Martin.

I was thinking on these lines e.g.

=INDIRECT(D2&ROUNDDOWN(SUM(COUNT(A2:A15)+1)*E2,0))

Where D2 contains the Column Letter and E2 the Percentage.

Based on

.... I would like to attach the column letter (A, B, C, etc.) ....

[martindwilson]

agreed, !!!!!