#### MassSpecGuru

##### Board Regular

- Joined
- Feb 23, 2010

- Messages
- 55

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