Results 1 to 3 of 3

Thread: Excel function for highest number for every 10 numbers?

  1. #1

    Excel function for highest number for every 10 numbers?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,338
    Articles
    0
    Excel Version
    2010 on Xubuntu
    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

  3. #3
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,338
    Articles
    0
    Excel Version
    2010 on Xubuntu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •