Help with Excel formula

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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

delaneyjm

Well-known Member
Joined
Apr 22, 2009
Messages
624
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Feb 23, 2010
Messages
55
Thanks, guys! :) You each get an extra drumstick! :LOL:

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top