Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average if
I'm trying to average the last 30 values in a column.
Column A contains a date and Column B contains a value for that date. A running average of the last 30 days (or 1-month) needs to be kept so that if new data is entered the average automatically updates. any ideas? I was thinking something like =AVERAGEIFS(B:B,A:A,LARGE(A:A,30)) but the greater than seems to muck things yup. thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
average if
What if there aren't 30 values to average?
Are there any empty cells within the range? -- Biff Microsoft Excel MVP "~slacker~" wrote in message ... I'm trying to average the last 30 values in a column. Column A contains a date and Column B contains a value for that date. A running average of the last 30 days (or 1-month) needs to be kept so that if new data is entered the average automatically updates. any ideas? I was thinking something like =AVERAGEIFS(B:B,A:A,LARGE(A:A,30)) but the greater than seems to muck things yup. thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
average if
no empty cells in the range.
it doesn't take long to get over 30 values but if it were less than thirty then i'd want to average the 20 or whatever values that there were. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
average if
Try this:
=IF(COUNT(B:B),AVERAGE(OFFSET(B2,COUNT(B:B)-1,,MAX(-COUNT(B:B),-30))),"") -- Biff Microsoft Excel MVP "~slacker~" wrote in message ... no empty cells in the range. it doesn't take long to get over 30 values but if it were less than thirty then i'd want to average the 20 or whatever values that there were. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
average if
thats perfect thanks!
although i don't understand the use of the if. seems it will always be true and is not needed. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
average if
The IF(COUNT(...)... makes sure there is at least 1 number in the range. If
there were no numbers to average then you'd get a #DIV/0! error. IF(COUNT(...)... prevents that error. You can remove it if you don't need it. -- Biff Microsoft Excel MVP "~slacker~" wrote in message ... thats perfect thanks! although i don't understand the use of the if. seems it will always be true and is not needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |