Select the values in a column range

[wstanle]

Hi,
I have a masters degree in software engineering so the concept should be simple to understand. However, I never used a spreadsheet to compute values. Here is what I am trying to do…

1. I have a spreadsheet filed with cell values from A1 to C500. I would like to select an arbitrary column range. Example: if I select cell A99 I would like to select the values in cells A93 to A99 and save the values in that range as a set.

2. Once I have that set, I would eliminate the highest and lowest value in that set and then get the average value in that set.

3. I would also like to save that value in column D so that the calculation for cell A??? is saved at cell D???

It probably would be easy to do in a general purpose programming language but I am trying to learn programming in a spreadsheet.

[Wanderer]

@wstanle

easy to do in a general purpose programming language but I am trying to learn programming in a spreadsheet.

I’d say it is not ‘general purpose’ but procedural vs functional programming. You might have seen glimpse of languages like lisp. So you need to adapt to find a function with the desired result. You will also find this type in SQL or prologue (don’t know, if anybody still uses this).
.
If something is not solvable this way, the you can use macros in BASIC or Python…

[fpy]

@wstanle

cells A93 to A99

A93:A99

@wstanle

average value in that set

in D93, put =AVERAGE(A93:A99)

@wstanle

highest and lowest value

a.k.a =MAX(A93:A99) and =MIN(A93:A99)

@wstanle

eliminate

then D93 if more likely =(SUM(A93:A99) - MAX(A93:A99) - MIN(A93:A99)) / 5

@wstanle

programming

maybe more coding, or abstracting, generalizing ?
e.g. 5 in the above is ROWS(A93:A99) - 2

@wstanle

trying to learn

note: gemini came up with =AVERAGEIFS(A93:A99, A93:A99, ">=" & SMALL(A93:A99, 2))

[wstanle]

Hi,
I guess that I have been thinking in terms of a procedural programming language. The answer you provided is exactly what I want! Now a second question.

Lets say that I add to the spreadsheet. How do I take this formula (That works and make all the rows compute this formula and store the results in the appropriate row in column D.

example:
for row 50 D50 should be =(SUM(A43:A50) - MAX(A43:A50) - MIN(A43:A50)) / 5
for row 51 D51 should be =(SUM(A44:A51) - MAX(A44:A51) - MIN(A44:A51)) / 5
… etc.
for row 102 D102 should be =(SUM(A95:A102) - MAX(A95:A102) - MIN(A95:A102)) / 5

Of course for rows 1 to 7 would be in error because there is not rows -6 to row 0. An error message for rows 1 to 7 might be acceptable.

I am an old school and retired programmer but I still can learn new things. Thanks a lot for setting me on the right path.

William Stanley

[fpy]

@wstanle

should be

indeed, that’s why it’s almost implicit in most cells operations : Moving Cells by Drag-and-Drop

and you probably should spend a bit of time to get familiar with Addresses and References, Absolute and Relative

maybe some youtube tutorials would be more accessible as a first dive

@wstanle

still can learn new things

sure.
wish you can even at some point Get involved !