Need formula to calculate average for every 30,60,90,120...etc.

[Murphyslaw]

Hi all,

I have a spreadsheet that has a column called production, and a column called days that is simply a count of days of production. I need to add another column that will give me the average of production for the first 30 days, then the average for 60 days, then the average for 90 days, and so on. Basically every 30 days Id like to see an average of the production so far. What would be the most simple formula to use for this?

Thanks in advance!

[Murphyslaw]

Anyone have any ideas? I tried looking for a solution and also working it out on my own but no formula I tried worked out. Any help on this would be greatly appreciated!

[MrShorty]

It is kind of hard to give specific suggestions without a sample worksheet to work with and without knowing what you have tried that has not worked.

My first thought is to use some variation of a =sumif()/countif().

[Murphyslaw]

Average.jpg


Here is a sample graph. In the "average" column im trying to calculate the average production over time for each well. I need it to be a total average of all days on production, so day 9 would be the average for that day and all days before, etc.

Is there any more information that would be helpful?

Thank you again!

[MrShorty]

How well do you understand absolute and relative cell references? A formula like =average($B$2:B2) copied down will give you a "running" average starting with the value in B2 down to whatever row the formula is copied to. Then, when you get to the next well, you edit the formula to reset the absolute reference and continue copying down.

[Murphyslaw]

I would have to do this for hundreds of wells, Id like to find a way to have it automatically identify its a new well and only pull production numbers for that particular one, until it gets to a new one.

[MrShorty]

How about going back to the original sumif/countif idea. Combined with the absolute/relative reference idea, we get something like =sumif($A$2:A2,A2,$B$2:B2)/countif($A$2:A2,A2)

[twiggywales]

can you upload a book it would make it much eaiser to help

[mnjofra]

Hi Murphyslaw,
if I understand well, you can solve this problem with following functions

Please Login or Register  to view this content.

(37th line of input...)

This shape gave you average at every 30x row of dateNO.

Example is in attachment.

Regards,