Forum Discussion

dustinhartley1979's avatar
dustinhartley1979
Copper Contributor
Jan 16, 2018
Solved

Assigning data a class and then performing calculations on that specific class

Hello,

I have assigned data in rows to either "class 1" or "class 2".  Without filtering the data, I am looking for a formula that will average only the data assigned to "class 1" and then I can adjust the parameters of the formula to average only the data assigned to "class 2".

 

I'm thinking an IF formula might work with some nesting.  Here is what I am thinking (with plain speak where a formula is needed):

 

=IF(A:A=1, then average the data only associated with class 1) and

=IF(A:A=2, then average the data only associated with class 2).

See attached file for some clarity (maybe).

 

Thanks for any help! 

  • Hi Dustin

     

    This formula might work for you:

     

    =AVERAGEIF(A1:A8,"1",B1:B8) <-- will only avg $ for Class 1

    =AVERAGEIF(A2:A8,"=1",C2:C8) <-- will only avg Weight for Class 1

     

    =AVERAGEIF(A1:A8,"2",B1:B8) <-- will only avg $ for Class 2

    =AVERAGEIF(A2:A8,"2",C2:C8) <-- will only avg Weight for Class 2

     

    I have attached the file you uploaded with the formula.

     

    Hopefully I have understood you right. Let us know how you go?

     

    Cheers

    Damien

6 Replies

  • Damien_Rosario's avatar
    Damien_Rosario
    Silver Contributor

    Hi Dustin

     

    This formula might work for you:

     

    =AVERAGEIF(A1:A8,"1",B1:B8) <-- will only avg $ for Class 1

    =AVERAGEIF(A2:A8,"=1",C2:C8) <-- will only avg Weight for Class 1

     

    =AVERAGEIF(A1:A8,"2",B1:B8) <-- will only avg $ for Class 2

    =AVERAGEIF(A2:A8,"2",C2:C8) <-- will only avg Weight for Class 2

     

    I have attached the file you uploaded with the formula.

     

    Hopefully I have understood you right. Let us know how you go?

     

    Cheers

    Damien

    • dustinhartley1979's avatar
      dustinhartley1979
      Copper Contributor

      Hi Damien,

      That works perfect for finding the average.  Thank you very much and for the quick response!  I also was needing to find the Median.  Guess what?  MEDIANIF is not a formula!!  Any insight as to how to go about finding that?

       

      Thanks again!

      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Hi Dustin

        Happy to help.

        For the Median, are you asking to calculate the Median for the $ and weight for both Class 1 and Class 2?

        Cheers
        Damien

Resources