Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD average cust calulation
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD average cust calulation
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD average cust calulation
Oops!
Doesn't display the array very well, I will send you the file directly. It will help you better. Mark. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
YTD average cust calulation
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another Time Calulation | Excel Discussion (Misc queries) | |||
CALULATION OF DURATION | Excel Discussion (Misc queries) | |||
Another Time Calulation Question - Sorry. | New Users to Excel | |||
how do I hide a calulation error in cells/table #div/0! | Excel Discussion (Misc queries) | |||
Getpivot calulation | Excel Worksheet Functions |