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?
22 Answers
This will do it:
=STDEV(INDIRECT(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(I7),")",""),"=AVERAGE(","")))FORMULATEXT extracts the formula in text formatSUBSTITUTE removes unnecessary parts to leave only H7:H8INDIRECT transforms that from just a string to a real referenceSTDEV performs the calculation
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.
