automate average calculation

**Mike_F** · 05-04-2017, 04:11 PM

I have a list of several hundred numbers, with one number added each day. Occasionally a number that is added is higher than all previous numbers. I am interested in calculating the average number of rows between these new record highs.

For example, in the list 0, 3, 5, -1, 0, 4, 9, 8, 7:
-- the new record highs are 3, 5 and 9
-- and the # of rows separating these records are, respectively, 1, 1 and 4 - for an average of (1+1+4)/3=2.

I currently calculate this in the following manner:
The numbers are in range A2:A10.
In cell B2, I have this formula, which I copy down:
=IF(A2=MAX(A$2:A2),ROW()-LOOKUP(2,1/(A1:A$2=LARGE(A1:A$2,1)),ROW(A1:A$2)),"")

And in cell F2, I have this formula:
=AVERAGEIF(B2:B10,">0")

That gives me the correct answer, but I want to eliminate the need for the column of data in column B. In other words, I want to automate this calculation so that with each daily entry in column A, my answer in F2 will be updated automatically.

Any ideas?

**bebo021999** · 05-04-2017, 08:55 PM

As my knowdledge, with formula, it is too complicate without helper column. VBA may helps? I will ask for other help.

**Mike_F** · 05-04-2017, 09:06 PM

thank you very much

**scottiex** · 05-04-2017, 09:41 PM

you would probably need VBA
In case that is workable - I threw this together - hopefully it'd work

Please Login or Register  to view this content.

**Mike_F** · 05-04-2017, 10:25 PM

Awesome -- many thanks!
Now all I have to do is learn how to use this code. I know zero about VBA, so that is now my challenge.
Thank you very much.

**CAntosh** · 05-05-2017, 10:04 AM

I was toying with this yesterday and came up with the following formula, which appears to be holding up. If your data is in A2:A10, array-enter (confirm with Ctrl + Shift + Enter instead of Enter) the formula below:

=AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8)+1)))

It seems to work for me, but if you need to be able to alter or adapt it with any frequency, it might be advisable to stick with the simpler helper column approach or use scottiex's VBA solution.

**Mike_F** · 05-05-2017, 12:45 PM

CAntosh: many thanks. I tried your idea and it does seem to work for the range a2:a10.

But unfortunately it does not update automatically when new values are added to the A column.

**CAntosh** · 05-05-2017, 01:07 PM

Sorry, Mike, I should have clarified - It's only written for A2:A10 in post#6, but if you want to be able to add numbers, just make the range much bigger than your data. Change the A10s to something huge, and change the A8s to two less than that. For instance, array-entering:

=AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998)+1)))

...will automatically incorporate new rows up to row 1000.

**Mike_F** · 05-05-2017, 01:18 PM

Amazing -- yes that does work!

Will use that.

At the same time, now I have an incentive to learn some VBA basics to try scottiex's idea.

Thanks to all.

Mike_F

**CAntosh** · 05-05-2017, 01:28 PM

Glad to help! And I strongly endorse developing a grasp of some basic VBA. There's a thread over in the VBA section with some excellent resources when you're ready to take the plunge:

https://www.excelforum.com/excel-pro...materials.html

**Mike_F** · 05-05-2017, 01:36 PM

great, tnx -- will check those out.

**CAntosh** · 05-05-2017, 04:52 PM

OP noted in a PM that an error results from inserting a new first row. This is a salient point I should've covered for any wandering across this thread. My response:

If you insert a new Row 1, Excel will automatically shift the ranges in my formula down a row. This adjustment is exactly what we want for the A2:A1000s, but not for the A1:A998s. For the formula to work, the A1:A998 range should always be one cell smaller than the big range of data (A2:1000) in size and it should always start in Row 1. If you're inserting a new row 1 and bumping your data down row, then the new formula you want would be:

=AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998)+1)))

So after inserting the row you would need to manually change the A2:A999s to A1:A998s in the formula or use a 'Replace All' to get them all at once (or copy/paste the above and array-confirm it). I could try to rewrite the formula so it responds better to Excel's IntelliSense updating, but I didn't want to make the formula uglier to parse than it already is. Stick to the "one smaller and starting in row 1" rule and you should be fine.

**Mike_F** · 05-08-2017, 02:26 PM

A further update to this thread:

After inserting one row, and changing the A2:A999s to A1:A998s, all is fine. The calculation produces the same result (in the example: 2), as it should.

But after inserting another row (and again changing the A2:A999s to A1:A998s, the calculation produces a different result: 3. This leaves me stumped.

Here's the formula I'm using after inserting the second row:

=AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998)+1)))

Mike_F

**CAntosh** · 05-09-2017, 10:28 AM

Good catch - that's one of those adaptability modifications I should have built in earlier. Basically, the "-1"s and "-2"s in the formula are based on the starting row, and I didn't build them to be relative in the original formula, so that part of the formula didn't adapt well to a new starting row. Sorry for the oversight. The "-1" parts should become, in your current example: "-ROW(A4)+1" and the "-2" parts should become "-ROW(A4)". They were actually wrong after your first inserted row, but the result wasn't affected. So the whole formula, array-entered, should be:

=AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998)+1)))

You'll still need to adjust the A1:A998 section manually when you insert a row, but the rest should update accordingly. Let me know if the new formula gives you any wonky results.

**Mike_F** · 05-09-2017, 01:00 PM

That has nailed it!

Works perfectly in my workbook. Have tested it in multiple ways. Excel is amazing.

Many thanks.

**CAntosh** · 05-09-2017, 01:02 PM

My pleasure!

automate average calculation

LinkBack

Thread Tools

Rate This Thread

Display

automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Re: automate average calculation

Thread Information

Users Browsing this Thread

Similar Threads

To automate Calculation of time taken

HELP: macro to automate a calculation

Excel 2007 : How to automate calculation of threshold value?

Automate Calculation with help of VBA

How to automate the calculation of the median from a frq distribut

[SOLVED] Automate simple calculation/formula

Help required to automate calculation

Bookmarks

Bookmarks

Posting Permissions