Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to use Indirect functions?

Does anyone have any suggestions on how to use Indirect function?
For Example, there is a list of numbers under column A, a given number 20 in
cell B1 and a formula sum(A81:A100)/20 in cell C1.
If I change the number 20 into 30 in cell B1, then the formula will become
sum(A71:A100)/30.
If I change the number 20 into 40 in cell B1, then the formula will become
sum(A61:A100)/40.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to use Indirect functions?

So you actually want the average?

One way:

=AVERAGE(OFFSET(A1,99,,-B1))

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to use Indirect function?
For Example, there is a list of numbers under column A, a given number 20
in
cell B1 and a formula sum(A81:A100)/20 in cell C1.
If I change the number 20 into 30 in cell B1, then the formula will become
sum(A71:A100)/30.
If I change the number 20 into 40 in cell B1, then the formula will become
sum(A61:A100)/40.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to use Indirect functions?

Thank everyone for suggestions
I would like to use Indirect structure instead of average.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

So you actually want the average?

One way:

=AVERAGE(OFFSET(A1,99,,-B1))

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to use Indirect function?
For Example, there is a list of numbers under column A, a given number 20
in
cell B1 and a formula sum(A81:A100)/20 in cell C1.
If I change the number 20 into 30 in cell B1, then the formula will become
sum(A71:A100)/30.
If I change the number 20 into 40 in cell B1, then the formula will become
sum(A61:A100)/40.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to use Indirect functions?

Try this:

=SUM(INDIRECT("A"&101-B1):A100)/B1

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Thank everyone for suggestions
I would like to use Indirect structure instead of average.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

So you actually want the average?

One way:

=AVERAGE(OFFSET(A1,99,,-B1))

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to use Indirect function?
For Example, there is a list of numbers under column A, a given number
20
in
cell B1 and a formula sum(A81:A100)/20 in cell C1.
If I change the number 20 into 30 in cell B1, then the formula will
become
sum(A71:A100)/30.
If I change the number 20 into 40 in cell B1, then the formula will
become
sum(A61:A100)/40.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions Mike Barlow Excel Worksheet Functions 7 May 21st 23 08:42 PM
INDIRECT & VLOOKUP functions Tanya Excel Worksheet Functions 4 October 26th 07 05:51 AM
indirect.ext / pull functions David Excel Discussion (Misc queries) 0 September 11th 07 02:29 PM
row-indirect functions [email protected] Excel Discussion (Misc queries) 3 March 1st 06 12:01 AM
Offset and Indirect functions Thrava Excel Discussion (Misc queries) 4 December 23rd 04 06:07 PM


All times are GMT +1. The time now is 05:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"