How to use indirect function?
 

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

How to use indirect function?
 

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" wrote in message
...
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

How to use indirect function?
 

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" wrote:

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" wrote in message
...
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

How to use indirect function?
 

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

"Bob Phillips" wrote in message ...
| 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" wrote in message
| ...
| 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
|
|
|

How to use indirect function?
 

try:

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

"Bob Phillips" wrote:

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" wrote in message
...
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

How to use indirect function?
 

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

"Niek Otten" wrote:

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

"Bob Phillips" wrote in message ...
| 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" wrote in message
| ...
| 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
|
|
|

How to use indirect function?
 

Thank everyone for suggestions
Eric

"Toppers" wrote:

try:

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

"Bob Phillips" wrote:

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" wrote in message
...
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