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]

Cf. post #5, column B (just the input data is different to what you posted later).

 

[ZochSteveo]

Cf. post #5, column B (just the input data is different to what you posted later).

Hmmmmm it does not seem to be working, "VSTACK" and "SEQUENCE" as functions do not appear to work? If I could attach a screenshot I would but it says that the image is too large!

 

[hagia_sofia]

I am not sure about VSTACK but SEQUENCE should definitely work in Excel 2024. The following should hopefully work in 2024 but the question above persists:

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

If you replace "" with a at the end it will return the original values too.

 

[ZochSteveo]

I am not sure about VSTACK but SEQUENCE should definitely work in Excel 2024. The following should hopefully work in 2024 but the question above persists:

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

If you replace "" with a at the end it will return the original values too.

There is an odd number in the data set so that should be ok. Thank you for your help. Bit worried about the SEQUENCE and LET commands, I will run any updates on excel that I have not completed and then go again and let you know how I get on

 

[Cubist]

VSTACK is available in 2024. The issue is your data set looks like text and not number due to the °C symbols. There needs to be further manipulation.

Book2
	A	B
1	19.742 °C
2		19.1805
3	18.619 °C
4		18.4905
5	18.362 °C
6		18.336
7	18.310 °C
8		18.296
9	18.282 °C
10		18.278
11	18.274 °C
12		18.298
13	18.322 °C
Sheet2

Cell Formulas
Range	Formula
B1:B13	B1	=IF(ISEVEN(SEQUENCE(ROWS(A1:A13))),VSTACK("",(TEXTBEFORE(A1:A12,"°C")+TEXTBEFORE(A3:A14,"°C"))/2),"")
Dynamic array formulas.

 

[LogChief]

=LET(
    x, B2:B30,
    MAP(x = "", SEQUENCE(ROWS(x)), LAMBDA(a,b, IF(a, AVERAGE(INDEX(x, b - 1), INDEX(x, b + 1)), INDEX(x, b))))
)

 

[ZochSteveo]

=LET(
    x, B2:B30,
    MAP(x = "", SEQUENCE(ROWS(x)), LAMBDA(a,b, IF(a, AVERAGE(INDEX(x, b - 1), INDEX(x, b + 1)), INDEX(x, b))))
)

View attachment 120574

For some reason it is not letting me use the LET function! This is so frustrating, any idea what could be going wrong?

 

[ZochSteveo]

=LET(
    x, B2:B30,
    MAP(x = "", SEQUENCE(ROWS(x)), LAMBDA(a,b, IF(a, AVERAGE(INDEX(x, b - 1), INDEX(x, b + 1)), INDEX(x, b))))
)

View attachment 120574

Got it working now, thank you very much for everyone's help!