Forum Discussion

RichardR2060's avatar
RichardR2060
Copper Contributor
Oct 07, 2025

Calculate Average Up Periods and Down Periods

Good afternoon, all.

I am doing an analysis on data going back to 1984 (monthly data). The data set has positive and negative numbers.

I am trying to create a formula that will go through all the data points and calculate the average of all the positive data points. Also, want the same calculation for all the negative data points. 

How should I set that up? What is the best function to use? 

 

Thank you in advance.

 

Regards,

community
Formulas and Functions
office 365

5 Replies

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 07, 2025

    RichardR2060

    Assuming you have real Number values (not Text values) formatted as %ages:

    With the above setup, in C15:

    =AVERAGE( FILTER( C2:C13, C2:C13 >= 0 ) )

    If you wan to exclude 0s, change >= with > in the above formula

    In C16:

    =AVERAGE( FILTER( C2:C13, C2:C13 < 0 ) )

     

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 07, 2025

    Hi

    A screenshot of your sheet would help. Also clarify if you want the averages by row, by column or something else

    • RichardR2060's avatar
      RichardR2060
      Copper Contributor
      Oct 07, 2025
      9/29/2025  2,313.19 0.84%
      8/29/2025  2,293.99 1.11%
      7/31/2025  2,268.72 0.32%
      6/30/2025  2,261.41 1.93%
      5/30/2025  2,218.67 1.66%
      4/30/2025  2,182.50(0.00%)
      3/31/2025  2,182.51(1.26%)
      2/28/2025  2,210.43 0.60%
      1/31/2025  2,197.29 1.42%
      12/31/2024  2,166.49(0.31%)
      11/29/2024  2,173.16 1.07%
      10/31/2024  2,150.10(0.44%)

       

      See above for an excerpt of the data set. Goes all the way back to 1984. Need the average for the third column (going from left to right) above.

Resources