YTD average cust calulation

[zy1972]

Hi, I have the following schedule. Basicall, I would like to drive my YTD
average customer calculation by input month.
Is there a simple formula to achieve that?
for example, if you input June, YTD average cust automated calculated as 4.72

YTD average cust is calculated as:
(Jan consump+Feb consump+...)/(Jan consump/Jan cust+Feb consump/Feb cust...)


Input month June

Jan Feb Mar Apr May YTD
consump 20.00 30.00 40.00 60.00 50.00 200.00
cust 2.00 4.00 5.00 7.00 6.00
cons/cust 10.00 7.50 8.00 8.57 8.33 42.40


YTD Average cust 4.72

 

[NoodNutt]

G'day zy1972 (No name submitted)

I think this is something what your looking for.

Column "N" is the overall monthly average = [YTD]/[Count] (Count = the
number of [Month Cells] that have a value >0.

A B C D E
F G H I J K
L M N O P

1 Jan Feb Mar Apr
May Jun Jul Aug Sep Oct Nov
Dec By Mth TYD Count

2 Consump 20.00 30.00 40.00 60.00 50.00
40.00 200.00 5

3Cust 2 .00 4.00 5.00 7.00
6.00
4.80 24.00 5

4

5Con/Cust 10.00 7.50 8.00 8.57 8.33
8.48 8.33 5


This is how it looks in formula view


A B C D E
F >>>> M N O
P

1 Jan Feb Mar Apr
May >>>> Dec By Mth TYD
Count

2 Consump 20.00 30.00 40.00 60.00 50.00
=O2/P2 =SUM(B2:M2) =COUNTIF(B2:M2,">0")

3Cust 2 .00 4.00 5.00 7.00
6.00 =O3/P3 =SUM(B2:M2)
=COUNTIF(B3:M3,">0")

4

5Con/Cust =IF(B3="","",(B2/B3)) copy to each month.
=SUM(B5:M5)/P5 =IF(O3="","",(O2/O3)) =COUNTIF(B5:M5,">0")


The Monthly Average & YTD will automatically update each time you enter new
values

HTH
Mark.

 

[NoodNutt]

Oops!

Doesn't display the array very well, I will send you the file directly.

It will help you better.

Mark.

 

[NoodNutt]

send me you direct email address to

(e-mail address removed)

I will then send you the file for you to look at.

HTH
Mark.