Range Reference
 

In the formula:

=AVERAGE(A1:A13)

I would like to be able to change the 13 to other values from a single cell
located in a different part of the spreadsheet by just entering a different
number in that cell. In other words to change the range from separate cell.
Can I do that without using a macro....kinda new at this. Many thanks.

Jerry

Range Reference
 

if you put a6 for example, in b1 and run this, it will but the average of A1:a6
in c1

sub test
Range("C1").Formula = "=average(a1:" & Range("b1").Value & ")"
end sub

just qualify the range with the sheet name and change the locations to your
preference

--


Gary


"Jerry" wrote in message
...
In the formula:

=AVERAGE(A1:A13)

I would like to be able to change the 13 to other values from a single cell
located in a different part of the spreadsheet by just entering a different
number in that cell. In other words to change the range from separate cell.
Can I do that without using a macro....kinda new at this. Many thanks.

Jerry

Range Reference
 

try:
=Average(Indirect("A1:A"&B1))

where B1 = 13. since you are not looking for a macro, the worksheet
function category probably would have been a better choice to post than the
programming section.


"Jerry" wrote:

In the formula:

=AVERAGE(A1:A13)

I would like to be able to change the 13 to other values from a single cell
located in a different part of the spreadsheet by just entering a different
number in that cell. In other words to change the range from separate cell.
Can I do that without using a macro....kinda new at this. Many thanks.

Jerry

Range Reference
 

Thanks for your help and advice.

Jerry

"Jerry" wrote:

In the formula:

=AVERAGE(A1:A13)

I would like to be able to change the 13 to other values from a single cell
located in a different part of the spreadsheet by just entering a different
number in that cell. In other words to change the range from separate cell.
Can I do that without using a macro....kinda new at this. Many thanks.

Jerry