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 |
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. |
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. |
YTD average cust calulation
|
All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com