90 day average that changes?

**Alfadirr** · 09-01-2009, 07:03 AM

Hello all!

I am trying to figure out how to figure a 90 day average from the last 3 values entered into a range.

I have tried many examples but nothing so far has worked. So I am posting here pleading for your help!

I want the average to change each time I enter new data. The columns are set up so that the first column (A) is the #, the second (B) is the person and columns C:L are months March - December. (Next year the months will be Jan - Dec of course)

I want a 90 day average column at the end that will only average the last 3 percentages entered. BUT - I also want it to know if there are only 1 or 2 numbers entered, to either return some sort of text or average it for 1 or 2 times.

For example:

If Jul and Aug have data but Jun does not, since there is not 3 months to average, I want it to either average 2 months or just "N/A". I would prefer to average just the 2 months, but would be fine with anything other then #Nul

I have attached the sheet I am working on, please help!

**zbor** · 09-01-2009, 07:33 AM

Sorry, not working

**masteff** · 09-01-2009, 03:47 PM

the xls worked for me.

you could try: =AVERAGE(INDEX(C2:L2,1,MAX(IF(C2:L2<>"",COLUMN(C2:L2)))-COLUMN(C2)+1-2),INDEX(C2:L2,1,MAX(IF(C2:L2<>"",COLUMN(C2:L2)))-COLUMN(C2)+1-1),INDEX(C2:L2,1,MAX(IF(C2:L2<>"",COLUMN(C2:L2)))-COLUMN(C2)+1))

It's an array formula, paste it into the cell and while in formula edit mode, hit shift-ctrl-enter.

You have to use the menu commands for copy and paste special to copy the array formula down to other cells.

I got the INDEX formula from exceltip.com and modified it from there.

**DonkeyOte** · 09-01-2009, 03:55 PM

You can get the Average without need for array if we assume that your data points are contiguous which the sample would imply, eg:

Please Login or Register  to view this content.

If you want to check for 0 numbers then add a COUNT test (via IF), eg:

Please Login or Register  to view this content.

You could also use OFFSET but that would be Volatile... hence my preference for INDEX.

**Alfadirr** · 09-01-2009, 07:24 PM

OOOPS!

I found these formulas before, but when filling down, they got all buggered. I didn't know you had to copy and past arrays in a special way. That fixed it, thanks so much!

**Alfadirr** · 09-01-2009, 07:45 PM

Hmmm, now I keep trying to tinker with "=AVERAGE(INDEX(B2:K2,1,MAX(IF(B2:K2<>"",COLUMN(B2:K2)))-COLUMN(B2)+1-2),INDEX(B2:K2,1,MAX(IF(B2:K2<>"",COLUMN(B2:K2)))-COLUMN(B2)+1))" to make another column to track a 60 day average, but I can't get it to work either. I broke the components down to try and understand whats going on. Could someone please tell me what each argument is doing, and then perhaps by understanding it I can change it correctly? It would be better then copying and pasting and learning nothing anyways.

Here is how I thought it splits up...

=AVERAGE
(INDEX
(B2:K2,1,MAX(IF(B2:K2<>"",COLUMN(B2:K2)))-COLUMN(B2)+1-2)
,INDEX
(B2:K2,1,MAX(IF(B2:K2<>"",COLUMN(B2:K2)))-COLUMN(B2)+1))

I thought removing 1 index would only make it average 2 columns, but, it didn't.

*edit*

Ok, after typing that all out it dawned on me, I think. I changed it to "=AVERAGE(INDEX(B2:K2,1,MAX(IF(B2:K2<>"",COLUMN(B2:K2)))-COLUMN(B2)+1-1),INDEX(B2:K2,1,MAX(IF(B2:K2<>"",COLUMN(B2:K2)))-COLUMN(B2)+1))"

**DonkeyOte** · 09-02-2009, 02:19 AM

Why are you using an Array ?

The only justification for an Array would be that you do indeed have blanks / non-numerics amongst your data points ? (eg data for Jan, none for Feb, data for Mar etc...)

If the above is not the case, ie all data points are consecutive they merely start at different points in each row, then for a 60 day average simply use:

Please Login or Register  to view this content.

The above will be more efficient.

90 day average that changes?

LinkBack

Thread Tools

Rate This Thread

Display

90 day average that changes?

Re: 90 day average that changes?

Re: 90 day average that changes?

Re: 90 day average that changes?

Re: 90 day average that changes?

Re: 90 day average that changes?

Re: 90 day average that changes?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions