formula (array or otherwise) to average data in one column in different size blocks

Hi All,

I have one column of data that appear in different size blocks, ie one set of data may have 5 rows of cells that contain data, a second block (range) may have 2 rows, a third range only 4 rows of data; the next range 8 rows of cells. There is no fixed sequence of size for these data blocks/ranges and it can vary. However between each set of data cells are 3 cells (contain text and/or formulae) with a number to demarcate the block of data. I need to average each set of data block. Here is a copy of what the data looks like in (say) column F. In column (say) B - I need to insert the average of the values 73.27 to 72.53 (in the first block); 72.54 to 72.62 (in the second block); 75.71 and 74.16 (in the third) and so on. Remember please the number of rows in each block can fluctuate (depending on the initial input of data


1

73.27
72.09
74.45
74.47
72.53

2

72.54
71.49
73.28
72.62

3

75.71
74.16

4

73.56
72.33
72.79
71.40
73.73

I can return the 'last value' in each block by using the formula: =IFERROR(OFFSET(INDEX(F1:F200,MATCH("X",F1:F200,0)),-2,0),""). Where "X" is the text number on its own below the block of data under examination. 200 is the maximum number of data rows.

I have been trying with all sorts of combinations but no real luck yet. Is there anyone out there t help?

Chris

OK!

The data looks like:

Column F
1

73.27
72.09
74.45
74.47
72.53

2

72.54
71.49
73.28
72.62

3

75.71
74.16

4

73.56
72.33
72.79
71.40
73.73

And I need to get the following:
Column A Column B
1 73.36 [average of 73.27 +72.09+74.45+74.47+72.53]
2 72.49
3 74.93
4 72.94

OK AliGW,

Not too sure if that helps unless I try to explain all the fotrmulae. But here goes (I am new to the forum so hope there is an attachment. External data is pasted into P1 and the spreadsheet automatically calculates the results so that the answers appear in column B

Chris

Hi again,

Just realised the latest attachment didn't have the correct workbook

See attached

Chris

Hi All,

I really appreciate all the replies. Forgive me -- I will get used to posting threads etc, It may be very difficult to 'explain' the details because the file and spreadsheet and formula are (in my opinion) quite complex. I post data (from an external source) into P1 then the spreadsheet calculates and produces the 'answer in column B. The latest 'Book 2' file illustrates some limited data that was pasted at P1. Then the column N has the blocks of data that I need to average. At the moment (current file ) the last value of each 'block' appears in column B.

I am looking for a formula to paste in column B that would average each block (in column N). ie B1 should have the average for N4:N8; B2 have the average for N12:N15; B3 the average for N19:N28; B4 the average of N24:N28.

Hi Glenn,

I am aware what the $ sign is for. I have just removed them in order for me to copy the formuls between columns (of which I have 30 or so in the final spreasdsheet).

Thanks AliGW -- message understood!!

In answer you your query the latest Book 2 file may be better to understand the request. The 'markers' 1,2,3 etc can be found in column A as 11, 12, 13 etc.... I have also tried to use the 'markers (in column N to attempt to write a formula). As you may notice I have used them to reproduce the last value in each block. Now I am trying to do the same for 'averaging'. each block (in column N). ie B1 should have the average for N4:N8; B2 have the average for N12:N15; B3 the average for N19:N20; B4 the average of N24:N28.

Thanks again AliGW

Chris

Hi AliGW,

They are indicated as 11, 12, 13 etc for internal editing because I have a similar 'block ' of data (with same formula and same calculations) which has 21, 22, 23, etc, and another set of data that has 31, 32, 33, 34 .....and so on (right up to n1, n2, n3...

Chris

With the first marker in A1, enter this array formula in B1, this works with your sample, but given the lack of clarity in your posts, may not work with anything else.

=IFERROR(AVERAGE(OFFSET($N$1:$N$200,MATCH(A1&"",$N$1:$N$200,0)+1,0,INDEX(FREQUENCY(IF(ISNUMBER($M$1:$M$200),ROW($M$1:$M$200)),IF(ISTEXT($N$1:$N$200)*LEN($N$1:$N$200),ROW($M$1:$M$200))),ROWS(B$1:B1)+1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Once the array is confirmed, fill down as needed.

Interesting results:

=AVERAGE(N4:N8) 60.32

=AVERAGE(OFFSET($N$1,3,0,5)) 60.32

=OFFSET($N$1,MATCH(A1&"",$N$1:$N$200,0)+1, 0,5) 60.69

=OFFSET($N$1,MATCH(A1&"",$N$1:$N$200,0), 0,5) 60.69

Jason's formula: 59.87


??????

Well spotted, John!

=AVERAGE(OFFSET($N$1:$N$200,MATCH(A1&"",$N$1:$N$200,0)+1,0,INDEX(FREQUENCY(IF(ISNUMBER($M$1:$M$200),ROW($M$1:$M$200)),IF(ISTEXT($N$1:$N$200)*LEN($N$1:$N$200),ROW($M$1:$M$200))),ROWS(B$1:B1)+1)))

Gives the correct result, but adding error suppression causes an anomaly. Using the formula above, indexing the frequency array returns 5, once the formula is wrapped in IFERROR, it returns {5}.

It appears that the single element array passed to OFFSET causes an error, which in turn causes the optional height parameter to be ignored, resulting in AVERAGE(OFFSET($N$1,3,0)).

As with most things, there is a workaround,

=IFERROR(AVERAGE(OFFSET($N$1:$N$200,MATCH(A1&"",$N$1:$N$200,0)+1,0,MIN(INDEX(FREQUENCY(IF(ISNUMBER($M$1:$M$200),ROW($M$1:$M$200)),IF(ISTEXT($N$1:$N$200)*LEN($N$1:$N$200),ROW($M$1:$M$200))),ROWS(B$1:B1)+1)))),"")

However, as is often the case, the suggestion is only as good as the information we are given to work with.

Why does this give wrong result?

=OFFSET($N$1,MATCH(A1&"",$N$1:$N$200,0)+1, 0,5) 60.69

Hi All,

Thanks for the comments (especially the recent ones from John and Jason)....sorry I have been away for a day or so -- but will 'analyse' all the threads and comment latere (maybe towards the weekend

Cheers

Chris

Hi again,

I have been away for a few weeks. I appreciate the assistance and have managed to 'combine' all of the suggestions from Jason/John and AliGW to come up with a formula that appears to work. The one issue with Johns 'formula' was the value '5' . This is fine if there are 5 rows or cells of data in the block. However if this number changes to 3 or even 1 then that formula will not work. Nevertheless I have adapted this formula with the following (pasted into B1).

=IFERROR(AVERAGE(OFFSET(N2,MATCH("1",N2:N200,0),,(MATCH("2",N2:N200,0)-MATCH("1",N2:N200,0)-2))),"")

The blocks of data to consider are in column 'N'. I have deliberately left out '$' because in my actual spreadsheet I need to copy column B to other parts of the spreadsheet. Also the above formula in B1 cannot be copied down the column because the markers "1", "2" etc, need to change in each row. Hence the formula in B2 will be:

=IFERROR(AVERAGE(OFFSET(N2,MATCH("2",N2:N200,0),,(MATCH("3",N2:N200,0)-MATCH("2",N2:N200,0)-2))),"")

Naturally this gets quite tedious if there a 'millions' of rows but fortunately I have a maximum of 20 rows so the task is not too bad. The formula may not be elegant and I am sure there are folk out there who could modify it in case one does have 'millions' of rows.

I attach the actual part of the spreadsheet

Thanks again

Chris

This one can be copied.

=IFERROR(AVERAGE(OFFSET(N$2,MATCH(RIGHT(A1),N$2:N$200,0),,(MATCH(RIGHT(--A2),N$2:N$200,0)-MATCH(RIGHT(A1),N$2:N$200,0)-2))),"")

The longer formula that I used earlier was based on no marker after the last block of numbers, however with the ending marker, this shorter formula works.

Thanks again to John, Jason and Flame Retired......all formula are useful if the markers are 'text'. In reply to John's query -- there is no reason why the markers are text as "1" etc, and my formula works equally well with either a number or text. Jasons/Johns adaptation only works with text markers. Could your formula work with numbers? How can one modify them. Flame Retired array formula is also OK with Text but I haven't been able to modify it for 'numbers'. As far as the second message from FlameRetired -- it isn'ty possible to add a column at 'O'.

Cheers -- appreciate all of the communication

Chris

If you want to use numbers, change formula in N which assigns numbers....

=IFERROR(IF(AND(M1="",M2="",M3=""),1,(-((K2-J2)*((RIGHT($P$1,4)/1000)/10))+M2*(VLOOKUP($P$1,$R$1:$S$200,2,FALSE))/((VLOOKUP(L2,$R$1:$S$200,2,FALSE))))),"")

formula in B

=IFERROR(AVERAGE(OFFSET($N$1:$N$200,MATCH(ROWS($1:1),$N$1:$N$200,0)+1,0,MIN(INDEX(FREQUENCY(IF(ISNUMBER($M$1:$M$200),ROW($M$1:$M$200)),IF(ISTEXT($N$1:$N$200)*LEN($N$1:$N$200),ROW($M$1:$M$200))),ROWS(B$1:B1)+1)))),"")