 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / July 2006Dynamic annual average
Hi, I keep a rough weekly diary on the development of a certain numeric value. Now I've got a series of data entries in a chart as follows: A1 - date of entry, eg. "23rd July 2006" A2 - reading data value, eg "100" I want to create a dynamic average on line A3 which tells me the average of data entries during the past year. Problem is, I've read the data value in irregular intervals and not every day (last year for example on 22nd July, the year before 10th July etc). My Excel understands the dates entered and draws a neat proportioned chart on development of data values for the whole period of surveillance, but I'd need to be able to see the effect of the latest reading on the dynamic average of the past year only. Is there a smart way of doing this? (the way I don't want to do it: create a column for every single day in calendar and always enter my data value in that column... that way I could easily subtract the value of 365 days ago from present value, but in 10 years I get a monster of a chart. There must be a better way!) 1. When you get past 256 entries, you'll run out of columns, so you really ought to use column A for dates, column B for values, and column C for the calculated averages. 2. I made an example with dates in column A, starting in A4 (leaving some room for headers and other information at the top). I put values in column B, starting in B4. I array-entered the following formula in C4, then copied it down as far as I had dates and values in columns A and B. To array enter a formula, type or paste the formula, then hold Ctrl+Shift while pressing Enter. If done correctly, Excel places the formula within {curly braces}. =SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4)))*($B$4:$B4))/SUM(($A$4:$A4<=$A4)*($A$4:$A4>DATE(YEAR($A4)-1,MONTH($A4),DAY($A4)))) - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > Hi, I keep a rough weekly diary on the development of a certain numeric > value. Now I've got a series of data entries in a chart as follows: [quoted text clipped - 16 lines] > could easily subtract the value of 365 days ago from present value, but > in 10 years I get a monster of a chart. There must be a better way!) Hi Jon, thanks for your advice. That with using a column for values should have been obvious, of course. I seem to have a problem inserting your formula: Excel gets stuck in the '...1,MONTH...' part and claims that there's an error in formula. When I try writing '+' instead of' commas, Excel claims that I've entered too few arguments (whatever that means). I spent some hours trying to work out what your formula actually does (I'm not sure yet) in order to understand the possible error. Now I'm in the conclusion that I don't get the DATE(YEAR(n)-1,MONTH(n),DAY(n)) - part to work properly - at least not in my Excel 2000: the result is not a date a year before. What could be wrong? Can you give me a hint? AS > 1. When you get past 256 entries, you'll run out of columns, so you really > ought to use column A for dates, column B for values, and column C for the [quoted text clipped - 15 lines] > http://PeltierTech.com > _______ Try this. In a blank cell, type =DATE(2006,7,25) and press Enter. The cell should now show the date. This is only part of the formula, of course. I can think of two sources of error: 1. Your dates are not in a recognizable format ("23rd July 2006"), so Excel may be assuming a numerical value of zero, thinking that you've entered text. YEAR(0)-1 will then produce an error. Use "23 July 2006" instead. 2. When you put my formula into the appropriate cell, you don't enter it using the Enter key, you do so by holding down Shift+Ctrl, them pressing the Enter key. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > Hi Jon, > [quoted text clipped - 40 lines] >> http://PeltierTech.com >> _______ Hi Jon, I got something working, thanks a million for your advice! For some reason I have to separate year, month and day by using semicolon, not comma as you suggest (ie '=DATE(2006;7;25)') but anyway the function works now. The result is not correct yet though: the figure I get for annual average should not be rising but it should stay on about same level, so something's not working correctly. I'm working on it (every now and then; I've got no deadline for this). Anssi |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.