MS Excel Question

I'm trying to do something like the following. Let's say I have a string of data in A1:A10. I
know I can take the average of these data (say in cell B1) 'AVERAGE(A1:A10)' but what I want
to do is make something a little more flexible. Sometimes I want to know the average between
A1:A4, sometimes I want to know the average between A3:A8, &c.

I want to have two cells (call them C1 and C2) whose values change the range of the average.
That is, I want to make the average A[C1]:A[C2], such that I can change C1 and C2 and get the
particular average I am looking for.

Any ideas?

Nemesio

Originally posted by Nemesio
I'm trying to do something like the following. Let's say I have a string of data in A1:A10. I
know I can take the average of these data (say in cell B1) 'AVERAGE(A1:A10)' but what I want
to do is make something a little more flexible. Sometimes I want to know the average between
A1:A4, sometimes I want to know the average between A3:A8, &c.

I want ...[text shortened]... change C1 and C2 and get the
particular average I am looking for.

Any ideas?

Nemesio

Originally posted by Ragnorak
Here's a good page explaining programming loops in VB...
http://computerprogramming.suite101.com/article.cfm/how_to_loop_in_cell_range_in_excel

D

Originally posted by Nemesio
I'm trying to do something like the following. Let's say I have a string of data in A1:A10. I
know I can take the average of these data (say in cell B1) 'AVERAGE(A1:A10)' but what I want
to do is make something a little more flexible. Sometimes I want to know the average between
A1:A4, sometimes I want to know the average between A3:A8, &c.

I want ...[text shortened]... change C1 and C2 and get the
particular average I am looking for.

Any ideas?

Nemesio

There may be an easier way, but I don't know of it.

In your sheet you want to use, press Alt+F11, bringing up the VB Editor.
Double click on your sheet in the left pane and then paste this code (all italics):


Sub getAvg()

Dim bottom, top

bottom = ActiveSheet.Range("C1" )
top = ActiveSheet.Range("C2" )

ActiveSheet.Range("E1" ).Select
ActiveCell.Value = "=average(" & bottom & ":" & top & " ) "

End Sub


Cell C1 will be the 'bottom' range value and C2 the top range value.
Cell E1 will hold the formula and end result.

Close the editor and back on your Excel sheet press Alt+F8 to bring up the macro dialog. You should see something like 'Sheet1.getAvg'. Just click Run.


Et voila.


EDIT You can change the cell values easily enough in the function to reflect what you need. Just make sure that you enter the range cell values (in the format: A1 or A12 etc.) in the correct cells after changing it in the function and be sure to remember the function will overwrite whatever was in the 'result' cell before running the macro.


EDIT! Stupid smileys! 😠

=AVERAGE(Indirect(C1):Indirect(C2))

Then just write the starting cell reference in C1 and the ending cell reference in C2.

Originally posted by Palynka
=AVERAGE(Indirect(C1):Indirect(C2))

Then just write the starting cell reference in C1 and the ending cell reference in C2.

Originally posted by Crowley
Ha! That's the one!

I knew there should be something like that...

Originally posted by Palynka
=AVERAGE(Indirect(C1):Indirect(C2))

Then just write the starting cell reference in C1 and the ending cell reference in C2.

Originally posted by Nemesio
Perfect! Thank you so much, Palynka.

And thanks so much to everyone else who tried to give me another way, albeit ones beyond
my Excel capacity.

You rock, Palynka!

Nemesio

Originally posted by Palynka
Stop it... You're making me blush... 😳

Originally posted by PBE6
Got any other tricks? I use Excel all the time (just ask those other nerds in the Posers & Puzzles forum), so any tricks will be put to good use, gar-ohn-teed!