Averaging data in a dataset

[ZochSteveo]

Hi everyone,

I was wandering if somebody could help me. I am currently doing some stitching for a dataset I have been given for my dissertation.

The data essentially is formatted like this with the following temperatures as an example:

17.1 in the first cell
13.2 in the third cell
14.5 in the fifth cell

What I then wanted was for the second cell to contain the average of the first and third and then the fourth cell to contain the average of the third and fifth etc.

I thought I had figured out how to average the first and third and third and fifth etc for the whole dataset as I need to find the average for each of the middle cells. The dataset is very large so would take far too long to do by hand. Like I said, I thought I had managed it originally but now it is coming up with an error message.

Please if anyone knows the solution, let me know

 

[hagia_sofia]

Hello, maybe something along these lines?

=LET(
a,A1:A10,
b,SEQUENCE(ROWS(a)),
IFERROR(IF(ISEVEN(b),(INDEX(a,b-1,0)+INDEX(a,b+1,0))/2,NA()),""))

 

[ZochSteveo]

Hello, maybe something along these lines?

=LET(
a,A1:A10,
b,SEQUENCE(ROWS(a)),
IFERROR(IF(ISEVEN(b),(INDEX(a,b-1,0)+INDEX(a,b+1,0))/2,NA()),""))

Thank you, can this be input into excel or does it have to be coded into the software? It appears to be code, which at this moment I am not currently using

 

[hagia_sofia]

It is an Excel formula so just select a cell where you want the output and then select the range where is your data.

 

[Cubist]

Not sure how you want to handle the last average cell where you have even # of rows. Another option:

Book2
	A	B	C	D	E
1	Even Rows Case			Odd Rows Case
2	2			2
3	5	2.5		5	2.5
4	3			3
5	4	7.5		4	7.5
6	12			12
7	2	6
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(ISEVEN(SEQUENCE(ROWS(A2:A7))),VSTACK("",(A2:A6+A4:A8)/2),"")
E2:E6	E2	=IF(ISEVEN(SEQUENCE(ROWS(D2:D6))),VSTACK("",(D2:D5+D4:D7)/2),"")
Dynamic array formulas.

 

[hagia_sofia]

Not sure how you want to handle the last average cell where you have even # of rows.

This probably depends on @ZochSteveo as it is a matter to be decided.

 

[ZochSteveo]

It is an Excel formula so just select a cell where you want the output and then select the range where is your data.

Would I be able to type it in for one cell and then click and drag it down? Hang on, let me attack a little spread sheet so that you have a better idea of what it is I am dealing with as I am a little bit confused at the moment. Attached below is the code, as you can see I have left a space in between for the averages of the two numbers and I need to do this for a vast dataset. Please could you assist with this bit as I think I have misunderstood how to do it.

Example for Questions.xlsx
	A
1	19.742 °C
2
3	18.619 °C
4
5	18.362 °C
6
7	18.310 °C
8
9	18.282 °C
10
11	18.274 °C
12
13	18.322 °C
Sheet1

 

[ZochSteveo]

Not sure how you want to handle the last average cell where you have even # of rows. Another option:

Book2
	A	B	C	D	E
1	Even Rows Case			Odd Rows Case
2	2			2
3	5	2.5		5	2.5
4	3			3
5	4	7.5		4	7.5
6	12			12
7	2	6
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=IF(ISEVEN(SEQUENCE(ROWS(A2:A7))),VSTACK("",(A2:A6+A4:A8)/2),"")
E2:E6	E2	=IF(ISEVEN(SEQUENCE(ROWS(D2:D6))),VSTACK("",(D2:D5+D4:D7)/2),"")
Dynamic array formulas.

Would I be able to type it in for one cell and then click and drag it down? Hang on, let me attack a little spread sheet so that you have a better idea of what it is I am dealing with as I am a little bit confused at the moment. Attached below is the code, as you can see I have left a space in between for the averages of the two numbers and I need to do this for a vast dataset. Please could you assist with this bit as I think I have misunderstood how to do it.

Separated Data.xlsx
	B
2	19.742 °C
3
4	18.619 °C
5
6	18.362 °C
7
8	18.310 °C
9
10	18.282 °C
11
12	18.274 °C
13
14	18.322 °C
Sheet2

I have sent this to both of you so hopefully one of you has a solution

 

[hagia_sofia]

Both of those formulas are single cell formulas, i.e. there is no need to drag anything down, only to place it in C2 and then select the range with the data, e.g. from B2:B14. Also, as @Cubist pointed out, the question is how the last average is to be calculated when there is even # of rows.

 

[ZochSteveo]

Both of those formulas are single cell formulas, i.e. there is no need to drag anything down, only to place it in C2 and then select the range with the data, e.g. from B2:B14. Also, as @Cubist pointed out, the question is how the last average is to be calculated when there is even # of rows.

Is there any chance you could upload this onto a mini spreadsheet just so I can see it visually? I really appreciate all of your help, but I appear to be struggling to get the formula to work as you describe. Many thanks for all of your support, I really do appreciate it