1

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.

example

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?

2
  • 1
    Btw, do you have to choose the two closest values manually? Closest to each other? Note sure if this will help but you can select the two values next to the median in an even numbered set using =MEDIAN(MIN(H7:H10),H7:H10) and =MEDIAN(MAX(H7:H10),H7:H10). – Mobus Apr 7 at 14:23
  • It would assist others trying to help you if you would prepare a sample worksheet that they could experiment with and save it on DropBox or OneDrive and post a view link with your question. – Charles Kenyon Apr 7 at 14:44
0

This will do it:

=STDEV(INDIRECT(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(I7),")",""),"=AVERAGE(","")))

FORMULATEXT extracts the formula in text format
SUBSTITUTE removes unnecessary parts to leave only H7:H8
INDIRECT transforms that from just a string to a real reference
STDEV performs the calculation

2
  • 1
    Thank you! I'm not sure why the two SUBSTITUTEs are needed, but that works! – Valerie Apr 7 at 16:24
  • One removes the part before the range, another removes the closing parenthesis. – ZygD Apr 8 at 6:18
0

Indeed, you can specify the reference as text in another column say N7:

="H7:H8"

Then in I7

=AVERAGE(INDIRECT(N7))

And in M7

=STDEV(INDIRECT(N7))

If you maybe choose two non-continuous references, you will need two helper columns say N7 and O7 with text values ="H7" and ="H10" in them respectively.

Then e.g.

=AVERAGE(INDIRECT(N7),INDIRECT(O7))

Of course in the latter approach you might as well put the two numbers you choose into N7 and O7 directly like =H7 and =H10 and drop the INDIRECT functions.

3
  • 1
    TAKE NOTE: INDIRECT is a volatile function. It will recalculate its answer with each and every change of any cell in any open workbook. This can slow down your spreadsheet considerably with large workbooks. – Mobus Apr 7 at 14:14
  • So would you advise against using INDIRECT? – Valerie Apr 7 at 16:24
  • Only if you notice a slow down. The more rows with INDIRECT functions you have, the slower it will get. But if you dont notice it why bother. The good news is you don't need to use it. By adding the two preferred data points in a helper column, you will probably simplify the way you select or change them and make it easier for someone else to understand your spreadsheet (even yourself 5 years from now). – Mobus Apr 8 at 6:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.