Help with Excel formula

[MassSpecGuru]

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

 

[delaneyjm]

You don't actually need to encase ADDRESS in TEXT as ADDRESS returns text.

You will need to wrap the results of your ADDRESS formulas with INDIRECT.

=AVERAGE(INDIRECT(ADDRESS(5,ROW()-2,4)&":"&ADDRESS(24,ROW()-2,4)))

 

[T. Valko]

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

Not sure why you're using the TEXT function.

Try it like this:

=AVERAGE(INDIRECT(ADDRESS(5,ROW()-2)&":"&ADDRESS(24,ROW()-2)))

 

[MassSpecGuru]

Thanks, guys! You each get an extra drumstick!

MSG

P.S. I swear I had already tried the formula you both came up with but it didn't work. I knew it involved the use of INDIRECT but it seemed that it was only returning the value of the cell in the E row, not the average of the entire column of 20 values. Odd. Maybe too much turkey.

Thanks again!

 

[barry houdini]

Wouldn't this be simpler with OFFSET or INDEX, e.g. in R5 copied down

=AVERAGE(INDEX(C$5:I$24,0,ROWS(R$5:R5)))