Excel function for highest number for every 10 numbers?

excell

New member
Joined
Dec 11, 2013
Messages
1
Reaction score
0
Points
0
Hi

Suppose I have a column filled with numbers in fraction. And correspondingly I have a specific name for every 10 numbers. I want to find out which is the maximum among the 10. For this I could use the "MAX" function in excel. Similarly I have to find the maximum among the next 10 numbers. Since I have about 200 such numbers under 20 different names and eight such sheets (10 numbers per name), it becomes difficult to type the formula and select the range everytime. I would like to know if there is any shortcut for getting this highest number among the 10 numbers.


Eg.
Name Value Number
TU 489.66 2.272
TU 380.45 0.0379
TU 331.97 0.0475
TU 309.64 1.004
TU 300.89 0.1215
TU 288.84 0.0501
TU 280.09 0.022
TU 278.58 0.0115
TU 270.16 0.0397
TU 267.76 0.0554

TB 423.82 1.8409
TB 310.75 0.0722
TB 286.47 0.2157
TB 280.73 0.0378
TB 279.88 0.0845
TB 271.35 0.2859
TB 255.82 0.0184
TB 250.11 0.0515
TB 246.73 0.0001
TB 245.47 0.0247

Thank you.

Please let me know if I am not clear
 
Supposing your data is in col A to C first data row is row2), in D3 enter =IF(C3<>"","",MAX(INDEX(($A$2:$A$100=A2)*($C$2:$C$100),0))) and pull down as needed - Also adapt the ranges to your needs
 
Back
Top