I have an excel sheet in which there are there are groups of 4 values in column H. So H7 to H10 contain 4 values relating to one sample. H11 to H14 contain 4 values relating to another sample.

In column I, I have a formula to calculate the average of 2 of the 4 values in H to generate a result. I choose the closest two values in H7 to H10. So I7 will contain the formula "=AVERAGE(H7:H8)". But I may click on I7 and then change the range if say H8 and H9 are closer to each other than H7 and H8 are. I have to manually choose the two consecutive values that most closely match each other.

In column M, I have a formula to calculate the standard deviation of 2 of the 4 values in H to generate a result. So M7 will contain the formula "=STDEV(H7:H8)". But if I click on I7 to change the range from which to calculate the average, I have to remember to also change to the same range in M7 so that the average and the standard deviation are calculated from the same two values in column H.

Is there a way to have the standard deviation formula in M7 automatically update based on the range of values I use to calculate the average in I7?

`=MEDIAN(MIN(H7:H10),H7:H10)`

and`=MEDIAN(MAX(H7:H10),H7:H10)`

. – Mobus Apr 7 at 14:23