excel averege from curent cell till previous non blank cell

0
votes

To continue my quest from: excel multiple average in single column

I have recently moved to excel 2013 and I have learned that my file has 180k rows therefore the suggested formula does not come into consideration (it uses to much memory and my pc crashes).

My idea of a solution is to make another column with a flag when the product number changes:

A | B | C | D

1 | 0:17| " " |" " |
1 | 0:18 | " " | " " |
.
.
1| 0:19 | 0:18 | 1 |
2| 1:12 | " " | " " |
2| 1:12 | " " | " " |
.
.
2| 1:13| 1:12.6| 1 |
3| 0:45| 0:45.0| 1 |

And make the average only between two non blank cell.How can get the address of the non blank cell from column E? How can I get the address of non blank cell without calculating the entire column? I have tried: Column C:

=IF(D1=1;AVERAGEIF((value in)E1:D1;A1;B$1:B1);"")

it should do an average from my current cell to the previous non blank cell in D - the last change in product name

Column D:

=IF(A2=A3;"";1)

Column E:

=ADDRESS(LOOKUP(1,1/(D:D<>""),ROW(D:D)),1)

to get the cell address of non blank cell, but again it calculates for the entire column so I have solved nothing.

Pleas help!

1
excel

1 Answers

0
votes

In Cell C1 put 1
In Cell C2 put =IF(A2=A1,C1+1,1) and copy down: this calculates the number of consecutive occurrences of the product number
In Cell D1 put =IF(A2<>A1,AVERAGE(OFFSET(B1,0,0,C1*-1,1)),"") and copy down
the Offset grabs the cells to average for the last of an adjacent block of product numbers.

This should be fast to calculate, but the downside is that OFFSET is volatile so column D will recalculate at every recalculation.