Averages on Excel2000

**ukphoenix** · 06-02-2009, 01:01 PM

Need a formula.

I have a total in F16 that i need to find an average of.

The range is set from f4 - f15 (12 months) and need to find the running average per month. but as we only have done 5 months of the year so far that is the only average required (F16/5).

I need this to count at the end of june (F16/6) basically not counting any zero months (jul, aug, sept etc until the end of each)

**ravishankar** · 06-02-2009, 01:12 PM

Hi
try
F16/(month(today())-1)
ravi

**NBVC** · 06-02-2009, 01:13 PM

maybe:

=Sum(F4:F15)/Month(Today())

This will sum all values and divide by the number of months up to including the current month... ie. June for today.

**ukphoenix** · 06-02-2009, 01:20 PM

=Sum(F4:F15)/Month(Today()) works but is dividing it by 6 months. June is currently 0 (zero). the division needs to happen on the last day of the month so the current answer required is the total divided by 5 months

Sorry if im explaining this too well

**NBVC** · 06-02-2009, 01:26 PM

How about?

=SUM(F4:F15)/(MONTH(TODAY())-IF(TODAY()=EOMONTH(TODAY(),0),0,1))

Note: EOMONTH() is an analysis toolpak addin function... Tools|Addins >> check Analysis Toolpak