# 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.

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.

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.
| >
| > 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.

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.