+ Reply to Thread
Results 1 to 4 of 4

How to average different rows of different groups?

  1. #1
    Registered User
    Join Date
    06-10-2014
    Posts
    2

    How to average different rows of different groups?

    Hello all,

    I have a data in the following format

    A 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.891146 0.108734 0.00001 0.00001 0.00001
    A 0.00001 0.010928 0.00001 0.00001 0.00001 0.00001 0.010163 0.00001 0.00001 0.315348 0.659607 0.003863 0.00001 0.00001
    A 0.017884 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.608175 0.373831 0.00001 0.00001 0.00001
    A 0.00001 0.009866 0.00001 0.00001 0.004547 0.00001 0.003794 0.00001 0.00001 0.392578 0.575197 0.002397 0.01155 0.00001
    A 0.00001 0.021613 0.00001 0.00001 0.00001 0.00001 0.00001 0.016251 0.00001 0.262446 0.699378 0.00001 0.000223 0.00001
    A 0.00001 0.000591 0.00001 0.00001 0.00001 0.00001 0.029345 0.00001 0.00001 0.387329 0.582635 0.00001 0.00001 0.00001
    A 0.00001 0.00001 0.01115 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.368629 0.620111 0.00001 0.00001 0.00001
    A 0.00001 0.010743 0.00001 0.00001 0.002378 0.00001 0.00001 0.00001 0.00001 0.225771 0.738416 0.018053 0.004559 0.00001
    A 0.00001 0.023161 0.00001 0.00001 0.016031 0.00001 0.00001 0.00001 0.00001 0.238668 0.72204 0.00001 0.00001 0.00001
    A 0.00001 0.017919 0.00001 0.003636 0.00001 0.00001 0.00001 0.00001 0.00001 0.299945 0.6784 0.00001 0.00001 0.00001
    A 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.027014 0.00001 0.312604 0.660271 0.00001 0.00001 0.00001
    A 0.00001 0.030373 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.301716 0.6678 0.00001 0.00001 0.00001
    A 0.003321 0.020748 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.381761 0.59407 0.00001 0.00001 0.00001
    B 0.00001 0.050104 0.00001 0.098948 0.00001 0.053074 0.00001 0.00001 0.774915 0.00001 0.022869 0.00001 0.00001 0.00001
    B 0.016258 0.091734 0.00001 0.059122 0.00001 0.035551 0.00001 0.00001 0.759745 0.00001 0.00001 0.00001 0.03751 0.00001
    B 0.00001 0.093645 0.00001 0.058207 0.00001 0.075342 0.00001 0.00001 0.763792 0.00001 0.00001 0.008925 0.00001 0.00001
    B 0.037132 0.082506 0.00001 0.00001 0.00001 0.006418 0.00001 0.00001 0.773647 0.00001 0.00001 0.055829 0.030906 0.013491
    B 0.00001 0.085847 0.00772 0.00001 0.00001 0.00001 0.033329 0.00001 0.809694 0.000095 0.00001 0.033829 0.00001 0.029416
    C 0.092266 0.00001 0.010997 0.049623 0.380852 0.011017 0.244976 0.047936 0.042348 0.010022 0.00001 0.00001 0.063346 0.046586
    C 0.114688 0.004414 0.00001 0.093292 0.402671 0.00001 0.19842 0.00001 0.00001 0.00001 0.00001 0.00478 0.161971 0.019704
    C 0.075297 0.00001 0.00001 0.03414 0.501045 0.00001 0.187556 0.005319 0.02793 0.00001 0.00001 0.00001 0.131885 0.036768
    C 0.115 0.00001 0.041102 0.065124 0.347882 0.05578 0.197517 0.032106 0.00001 0.00001 0.00001 0.002207 0.143232 0.00001
    C 0.152739 0.025286 0.010477 0.00001 0.406542 0.02176 0.219094 0.044572 0.00001 0.01274 0.00001 0.00001 0.103988 0.002762
    C 0.191319 0.00001 0.00001 0.079165 0.343816 0.00648 0.141037 0.041638 0.022693 0.00001 0.00001 0.026643 0.109719 0.037449
    C 0.102538 0.00001 0.088414 0.078552 0.379499 0.031857 0.182849 0.00001 0.020398 0.00001 0.00001 0.010135 0.093655 0.012064
    C 0.096661 0.00001 0.070106 0.028832 0.330699 0.020614 0.20455 0.04461 0.00001 0.006313 0.008697 0.00001 0.105221 0.083666
    E 0.814651 0.00001 0.00001 0.00001 0.151833 0.00001 0.007088 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.026328
    E 0.744489 0.00001 0.00001 0.00001 0.148372 0.045737 0.00001 0.00001 0.01611 0.00001 0.00001 0.003001 0.00001 0.04221
    E 0.774646 0.00001 0.00001 0.00001 0.207946 0.006951 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.010358
    E 0.729396 0.00001 0.00001 0.00001 0.188963 0.00001 0.012583 0.035127 0.00001 0.00001 0.00001 0.032131 0.001721 0.00001
    E 0.692873 0.00001 0.00001 0.00001 0.20956 0.018129 0.00001 0.028463 0.00001 0.00001 0.00001 0.03228 0.018616 0.00001
    E 0.704478 0.00001 0.010907 0.00001 0.249692 0.001928 0.012418 0.015179 0.00001 0.00001 0.00001 0.004066 0.001271 0.00001
    E 0.9248 0.00001 0.00001 0.00001 0.050094 0.00001 0.00001 0.010573 0.00001 0.00001 0.00001 0.014433 0.00001 0.00001
    E 0.840834 0.00001 0.03822 0.00001 0.079759 0.00001 0.032087 0.00001 0.00001 0.00001 0.00001 0.009004 0.000016 0.00001
    E 0.692594 0.00001 0.00001 0.00001 0.133273 0.049138 0.057034 0.064086 0.00001 0.00001 0.00001 0.00001 0.00001 0.003795
    E 0.723544 0.00001 0.002573 0.003711 0.192158 0.00001 0.008097 0.022146 0.00001 0.00001 0.00001 0.018683 0.029029 0.00001
    E 0.775596 0.00001 0.00001 0.00001 0.190097 0.00001 0.034197 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001
    E 0.719302 0.00001 0.00001 0.00001 0.159784 0.029647 0.021259 0.069918 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001
    E 0.714902 0.00001 0.00001 0.00001 0.173917 0.066072 0.007017 0.001892 0.00001 0.00001 0.004867 0.001202 0.030071 0.00001
    E 0.671543 0.00001 0.00001 0.038609 0.154937 0.00001 0.019387 0.067642 0.00001 0.00001 0.00001 0.012553 0.035259 0.00001
    E 0.734558 0.00001 0.039882 0.00001 0.160925 0.018354 0.04619 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001
    E 0.748396 0.00001 0.00001 0.00001 0.224896 0.00001 0.00001 0.005307 0.00001 0.00001 0.00001 0.00001 0.021301 0.00001
    E 0.946783 0.00001 0.00001 0.00001 0.000839 0.052268 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001 0.00001
    E 0.929277 0.00001 0.00001 0.00001 0.032037 0.00108 0.00001 0.00001 0.00001 0.00001 0.00001 0.037506 0.00001 0.00001
    E 0.804572 0.00001 0.006693 0.00001 0.08216 0.008646 0.015561 0.072668 0.00001 0.00001 0.00001 0.00963 0.00001 0.00001
    And now I need a function which can average separately all 14 rows for A, B, C, D and E groups.

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to average different rows of different groups?

    You could use text to columns with a space as separator and then the normal way?
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    06-10-2014
    Posts
    2

    Re: How to average different rows of different groups?

    Thanks. This is just an example. I have thousands of samples with A to Z series and it is a pain to average one by one

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: How to average different rows of different groups?

    Not sure if your data on the same row is in the same cell (in which case do what Nathan suggested first)
    If your data is now segregated and your "Category" is in Column A

    =SUMPRODUCT(--($A$1:$A$100="B")*$B$1:$M$100)/SUMPRODUCT(--($A$1:$A$100="B"))
    where your numbers are in columns B to M
    If you want to reference a cell for the category instead of hardcoding it
    =SUMPRODUCT(--($A$1:$A$100=H1)*$B$1:$M$100/SUMPRODUCT(--($A$1:$A$100=H1))
    where H1 contains B
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 16
    Last Post: 05-22-2019, 11:48 PM
  2. Average of Groups
    By penfold in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-02-2010, 08:50 AM
  3. [SOLVED] MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how?
    By || cypher || in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  4. MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how?
    By || cypher || in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  5. [SOLVED] MAX, MIN, MEAN, AVERAGE, MODE for multiple groups...how?
    By || cypher || in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-14-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1