Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | | | |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 | | | |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
indirect function | Excel Worksheet Functions | |||
using the INDIRECT function | Excel Discussion (Misc queries) | |||
Help with Indirect function ? | Excel Worksheet Functions | |||
Function INDIRECT | Excel Worksheet Functions | |||
Help with INDIRECT function. | Excel Worksheet Functions |