Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ever changing averages, depending on date.
I asked about this before, but didn't really help, so I'll try rephrasing
There are three columns, one for dates (Column A), another for amounts on each date (Column B), including dates in the future which will be left as default (0) until a value is entered on that date and another for rolling average amounts (Column C), for dates past, present and future. Row 1 has column headings and Row 2 is a spacer row. Data starts in Row 3 I am seeking a formula in the average column (first cell will be C3 and replicated downwards) that will return an average between two dates and populate that average for every date in the future, but as values of future dates, which have not been entered yet will by default be 0 should not affect the average until after that date occurs. Yet values entered for future dates will not affect the averages for past dates. Example- After one day has passed, it will populate all the cells in the average column with that value (as that is an average of one number). After two days have passed, it will return an average of those two days for the cell representing the average for the second date and the third date and all dates in the future. After twenty days have passed, it will return an average of all amounts split over those 20 days for the cell representing the average for the twentieth date and all dates in the future. etc. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ever changing averages, depending on date.
How about a mini example data set?
Bernard "John Smith" wrote in message ... I asked about this before, but didn't really help, so I'll try rephrasing There are three columns, one for dates (Column A), another for amounts on each date (Column B), including dates in the future which will be left as default (0) until a value is entered on that date and another for rolling average amounts (Column C), for dates past, present and future. Row 1 has column headings and Row 2 is a spacer row. Data starts in Row 3 I am seeking a formula in the average column (first cell will be C3 and replicated downwards) that will return an average between two dates and populate that average for every date in the future, but as values of future dates, which have not been entered yet will by default be 0 should not affect the average until after that date occurs. Yet values entered for future dates will not affect the averages for past dates. Example- After one day has passed, it will populate all the cells in the average column with that value (as that is an average of one number). After two days have passed, it will return an average of those two days for the cell representing the average for the second date and the third date and all dates in the future. After twenty days have passed, it will return an average of all amounts split over those 20 days for the cell representing the average for the twentieth date and all dates in the future. etc. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ever changing averages, depending on date.
"Bernard Liengme" wrote in message ... How about a mini example data set? Bernard 28Jan2010---21---21 29Jan2010---37---29 30Jan2010---23---27 31Jan2010---39---30 (today) 01Feb2010----0---30 02Feb2010----0---30 02Feb2010----0---30 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ever changing averages, depending on date.
With your data starting in A1
In C1: + B1 In C2: =IF(B2,AVERAGE($B$1:B2),C1) 'note the absolute reference on B1 This is copied down the column You might be more comfortable with =IF(B20, bit Excel will take any numeric value as meaning TRUE in a formula such as =IF(B2, ..... best wishes Bernard "John Smith" wrote in message ... "Bernard Liengme" wrote in message ... How about a mini example data set? Bernard 28Jan2010---21---21 29Jan2010---37---29 30Jan2010---23---27 31Jan2010---39---30 (today) 01Feb2010----0---30 02Feb2010----0---30 02Feb2010----0---30 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing font colour depending on cell value | Excel Worksheet Functions | |||
Changing Colour of Bar depending on Value | Charts and Charting in Excel | |||
changing the colour of cells depending on the content. | Excel Discussion (Misc queries) | |||
Changing cell colour depending on another cells value... | Excel Discussion (Misc queries) | |||
changing a cell value depending on an If Function/Lookup | Excel Worksheet Functions |