Forum Discussion

nickpant's avatar
nickpant
Copper Contributor
Oct 29, 2019

Averaging data

hello,   I am new in this forum. I want to average every 143 cell from a raw data which has around 300000 cells. Is any way to do it without writting the command every 143 cells?  Thank you for yo...
excel
office 365
training
tauqeeracma's avatar
tauqeeracma
Iron Contributor
Oct 29, 2019

Hi nickpant 

 

As per my understanding you need to get average with the gap of 143 rows, if this is the case you can use below formula:

=AVERAGE(OFFSET($C$5:$C$300004,((ROW(C5)-ROW($C$5))*143),0,1,1):OFFSET($C$5:$C$300004,

((ROW(C5)-ROW($C$5))*143),0,143,1))

 

A sample file is also attached for your reference, please let me know if it works for you.

 

Thanks

Tauqeer

nickpant's avatar
nickpant
Copper Contributor
Oct 30, 2019

tauqeeracma 

thanks for your help! I get the average value but when i tried to drag so i can get for the other groups of 143 cells the average i was geting was the same. 

Is anything i might need to change in the excel settings?

Thank you for your time!

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Oct 30, 2019

    HI nickpant 

     

    I would suggest you to please share your file or sample data so I can adjust the required formula for you.

     

    Thanks

    Tauqeer

    • nickpant's avatar
      nickpant
      Copper Contributor
      Nov 01, 2019

      tauqeeracma 

      This is the file i am working on. The averages i am calculating is for column C. The command you send me works but when i drag to get the average for the next group i get the same value.

      Thank you 

      • tauqeeracma's avatar
        tauqeeracma
        Iron Contributor
        Nov 02, 2019

        Hi nickpant 

         

        Calculation options in your file are set as 'Manual'.

         

        Please change it to 'Automatic'.

        I have also applied the same formula in your file its working fine and giving different values.

        Please check attached file and let me know if it works for you.

         

        Thanks

        Tauqeer

Resources