How to use indirect function?

G

Guest

Does anyone have any suggestions on how to use indirect function for my case?

There is a list of numbers under column A, and there is a input value in
cell B1
On column C, I would like to sum up the numbers on upside and downside.
For example,
In column A, and the input value in cell B1 is 4, then in cell C9, I should
sum up all values between [A5] and [A13], for cell C9 = 4+1+8+4+5+6+0+9+8 =
37
[A1] 5
[A2] 8
[A3] 9
[A4] 3
[A5] 4
[A6] 1
[A7] 8
[A8] 4
[A9] 5
[A10] 6
[A11] 0
[A12] 9
[A13] 8
[A14] 3
[A15] 2
[A16] 5
[A17] 7

Using this approach, I would like to determine the rest of number under
column C.
For exception, in cell A4, there is no 4 addition rows on upside, so it
should return "". In cell A15, there is no 4 addition rows on downside, so
it should return "".
N.B. The number of row to sum up on upside and downside is a variable based
on the input value in cell B1.

This coding is too complicated to me, can anyone please help for any
suggestions?
Thank in advance for any suggestions
Eric
 
B

Bob Phillips

You haven't explained (at least to me) fully what you mean by upside and
downside.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
G

Guest

Bob,
I translate upside/downside as an offset (-/+) from the result
cell. In the example, offset 4 (in B1) from 9 (C9) to give 5 and 13: so SUM
is A5:A13.

I'll leave the solution to you!


Bob Phillips said:
You haven't explained (at least to me) fully what you mean by upside and
downside.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Eric said:
Does anyone have any suggestions on how to use indirect function for my
case?

There is a list of numbers under column A, and there is a input value in
cell B1
On column C, I would like to sum up the numbers on upside and downside.
For example,
In column A, and the input value in cell B1 is 4, then in cell C9, I
should
sum up all values between [A5] and [A13], for cell C9 = 4+1+8+4+5+6+0+9+8
=
37
[A1] 5
[A2] 8
[A3] 9
[A4] 3
[A5] 4
[A6] 1
[A7] 8
[A8] 4
[A9] 5
[A10] 6
[A11] 0
[A12] 9
[A13] 8
[A14] 3
[A15] 2
[A16] 5
[A17] 7

Using this approach, I would like to determine the rest of number under
column C.
For exception, in cell A4, there is no 4 addition rows on upside, so it
should return "". In cell A15, there is no 4 addition rows on downside,
so
it should return "".
N.B. The number of row to sum up on upside and downside is a variable
based
on the input value in cell B1.

This coding is too complicated to me, can anyone please help for any
suggestions?
Thank in advance for any suggestions
Eric
 
N

Niek Otten

Also, the sum of A5:A13 is 45, not 37. And why A13? Is that 4 lines from the bottom?
Do all rows in C have to have a sum? From what to what?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| You haven't explained (at least to me) fully what you mean by upside and
| downside.
|
| --
| HTH
|
| Bob
|
| (there's no email, no snail mail, but somewhere should be gmail in my addy)
|
| | > Does anyone have any suggestions on how to use indirect function for my
| > case?
| >
| > There is a list of numbers under column A, and there is a input value in
| > cell B1
| > On column C, I would like to sum up the numbers on upside and downside.
| > For example,
| > In column A, and the input value in cell B1 is 4, then in cell C9, I
| > should
| > sum up all values between [A5] and [A13], for cell C9 = 4+1+8+4+5+6+0+9+8
| > =
| > 37
| > [A1] 5
| > [A2] 8
| > [A3] 9
| > [A4] 3
| > [A5] 4
| > [A6] 1
| > [A7] 8
| > [A8] 4
| > [A9] 5
| > [A10] 6
| > [A11] 0
| > [A12] 9
| > [A13] 8
| > [A14] 3
| > [A15] 2
| > [A16] 5
| > [A17] 7
| >
| > Using this approach, I would like to determine the rest of number under
| > column C.
| > For exception, in cell A4, there is no 4 addition rows on upside, so it
| > should return "". In cell A15, there is no 4 addition rows on downside,
| > so
| > it should return "".
| > N.B. The number of row to sum up on upside and downside is a variable
| > based
| > on the input value in cell B1.
| >
| > This coding is too complicated to me, can anyone please help for any
| > suggestions?
| > Thank in advance for any suggestions
| > Eric
| >
|
|
 
G

Guest

try:

=SUM(INDIRECT("A" & ROW()-$B$1 &":A" &ROW()+$B$1))

Bob Phillips said:
You haven't explained (at least to me) fully what you mean by upside and
downside.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Eric said:
Does anyone have any suggestions on how to use indirect function for my
case?

There is a list of numbers under column A, and there is a input value in
cell B1
On column C, I would like to sum up the numbers on upside and downside.
For example,
In column A, and the input value in cell B1 is 4, then in cell C9, I
should
sum up all values between [A5] and [A13], for cell C9 = 4+1+8+4+5+6+0+9+8
=
37
[A1] 5
[A2] 8
[A3] 9
[A4] 3
[A5] 4
[A6] 1
[A7] 8
[A8] 4
[A9] 5
[A10] 6
[A11] 0
[A12] 9
[A13] 8
[A14] 3
[A15] 2
[A16] 5
[A17] 7

Using this approach, I would like to determine the rest of number under
column C.
For exception, in cell A4, there is no 4 addition rows on upside, so it
should return "". In cell A15, there is no 4 addition rows on downside,
so
it should return "".
N.B. The number of row to sum up on upside and downside is a variable
based
on the input value in cell B1.

This coding is too complicated to me, can anyone please help for any
suggestions?
Thank in advance for any suggestions
Eric
 
G

Guest

Yes, the sum of A5:A13 is 45, not 37, In cell A15, there is no 4 addition
rows on downside, so it should return "".
Thank everyone for suggestions
Eric
 
G

Guest

Thank everyone for suggestions
Eric

Toppers said:
try:

=SUM(INDIRECT("A" & ROW()-$B$1 &":A" &ROW()+$B$1))

Bob Phillips said:
You haven't explained (at least to me) fully what you mean by upside and
downside.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Eric said:
Does anyone have any suggestions on how to use indirect function for my
case?

There is a list of numbers under column A, and there is a input value in
cell B1
On column C, I would like to sum up the numbers on upside and downside.
For example,
In column A, and the input value in cell B1 is 4, then in cell C9, I
should
sum up all values between [A5] and [A13], for cell C9 = 4+1+8+4+5+6+0+9+8
=
37
[A1] 5
[A2] 8
[A3] 9
[A4] 3
[A5] 4
[A6] 1
[A7] 8
[A8] 4
[A9] 5
[A10] 6
[A11] 0
[A12] 9
[A13] 8
[A14] 3
[A15] 2
[A16] 5
[A17] 7

Using this approach, I would like to determine the rest of number under
column C.
For exception, in cell A4, there is no 4 addition rows on upside, so it
should return "". In cell A15, there is no 4 addition rows on downside,
so
it should return "".
N.B. The number of row to sum up on upside and downside is a variable
based
on the input value in cell B1.

This coding is too complicated to me, can anyone please help for any
suggestions?
Thank in advance for any suggestions
Eric
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top