MassSpecGuru
Board Regular
- Joined
- Feb 23, 2010
- Messages
- 55
Hi, all!
I'm trying to develop a single formula (that I can use to fill down or across) to replace entering different formulas in every cell of the range, however, I'm running into a little problem and need a little assistance.
Background: In cells C5:I24 are columns of replicate measurements that I need to get some simple statistics on. Intead of typing different formulas "=AVERAGE(C5:C24)", "=AVERAGE(D5:D24)", etc. in cells R5, R6, etc., I want to have just one formula that won't change when I copy or fill down. I have come up with a formula that displays the desired range, however, when I prepend the "AVERAGE" function I get an "#VALUE!" error message:
=TEXT(ADDRESS(5,ROW()-2,4),)&":"&TEXT(ADDRESS(24,ROW()-2,4),)
Thus, if enter the above formula in cell R5 I get "C5:C24" as desired, however, if I enter:
=AVERAGE(TEXT(ADDRESS(5,ROW()-2,4),)&":"&TEXT(ADDRESS(24,ROW()-2,4),))
in R5 I get "#VALUE!" instead of the numerical average.
Any ideas how to correct my formula? If not, any ideas how to do it another way?
Thanks and Happy Thanksgiving!
MSG
I'm trying to develop a single formula (that I can use to fill down or across) to replace entering different formulas in every cell of the range, however, I'm running into a little problem and need a little assistance.
Background: In cells C5:I24 are columns of replicate measurements that I need to get some simple statistics on. Intead of typing different formulas "=AVERAGE(C5:C24)", "=AVERAGE(D5:D24)", etc. in cells R5, R6, etc., I want to have just one formula that won't change when I copy or fill down. I have come up with a formula that displays the desired range, however, when I prepend the "AVERAGE" function I get an "#VALUE!" error message:
=TEXT(ADDRESS(5,ROW()-2,4),)&":"&TEXT(ADDRESS(24,ROW()-2,4),)
Thus, if enter the above formula in cell R5 I get "C5:C24" as desired, however, if I enter:
=AVERAGE(TEXT(ADDRESS(5,ROW()-2,4),)&":"&TEXT(ADDRESS(24,ROW()-2,4),))
in R5 I get "#VALUE!" instead of the numerical average.
Any ideas how to correct my formula? If not, any ideas how to do it another way?
Thanks and Happy Thanksgiving!
MSG