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 ad...
Formulas & Functions
Need Help
  • Damien_Rosario's avatar
    Damien_Rosario
    Jan 16, 2018

    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
Jan 17, 2018

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
Jan 17, 2018
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
    • dustinhartley1979's avatar
      dustinhartley1979
      Copper Contributor
      Jan 17, 2018

      Hi Damien,

       

      I think I found this formula that works for the Median for cost:

       

      =MEDIAN(IF(A2:A8=1,B2:B8,""))

      Using Ctrl Shift Enter to input the formula.

       

      Thanks again for your help!

      • Damien_Rosario's avatar
        Damien_Rosario
        Silver Contributor
        Jan 17, 2018

        Hi Dustin

        Great to hear you have found it. That's the formula I was thinking of as well.

        And sorry for the delay, I was running a Microsoft Teams training session to 35 people so have only just finished now!

        Best of luck buddy.

        Cheers
        Damien

Resources