| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How can I vary the row number or column alphabet depending on my n
Example- Varying row number.
If I need to look up cell A(x) where, A is the column alphabet. x is the variable row number. How can I input x as a variable? OR Example- Varying column alphabet. If I need to look up cell (x)1 where, x is the variable column alphabet. 1 is the row number. How can I input x as a variable? Thank you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I vary the row number or column alphabet depending on my n
Generally speaking...
=INDEX(range,R,C) Whe R = row number C = column number Note that both R and C are *relative* to the range. For example: =INDEX(F5:J10,2,5) Returns the value at cell address J6. J6 is on the 2nd row in the 5th column relative to the range F5:J10. Both R and C can be calculated in many ways limited only by your imagination and/or skill level. You can also use this technique on a one dimensional array (single row or single column). =INDEX(A:A,10) Returns the value of cell A10 =INDEX(1:1,10) Returns the value of cell J1 You can use a range as an entire column (A:A) or row (1:1) as I did above or you can use a specific range. Just remember that R and C are *relative* to the range. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Example- Varying row number. If I need to look up cell A(x) where, A is the column alphabet. x is the variable row number. How can I input x as a variable? OR Example- Varying column alphabet. If I need to look up cell (x)1 where, x is the variable column alphabet. 1 is the row number. How can I input x as a variable? Thank you |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I vary the row number or column alphabet depending on
Thanks for the response Valko.
Actually I was looking to sum up specific number of cells by varying the row number or column alphabet. Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on another condition or sum rows A2:A7 based on a third condition. Hence I wanted to be able to vary the row number. Please advise if you think this is possible. Thanks! "T. Valko" wrote: Generally speaking... =INDEX(range,R,C) Whe R = row number C = column number Note that both R and C are *relative* to the range. For example: =INDEX(F5:J10,2,5) Returns the value at cell address J6. J6 is on the 2nd row in the 5th column relative to the range F5:J10. Both R and C can be calculated in many ways limited only by your imagination and/or skill level. You can also use this technique on a one dimensional array (single row or single column). =INDEX(A:A,10) Returns the value of cell A10 =INDEX(1:1,10) Returns the value of cell J1 You can use a range as an entire column (A:A) or row (1:1) as I did above or you can use a specific range. Just remember that R and C are *relative* to the range. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Example- Varying row number. If I need to look up cell A(x) where, A is the column alphabet. x is the variable row number. How can I input x as a variable? OR Example- Varying column alphabet. If I need to look up cell (x)1 where, x is the variable column alphabet. 1 is the row number. How can I input x as a variable? Thank you |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I vary the row number or column alphabet depending on
Ok, you keep saying "based on a condition" but you don't say what that
condition might be. So, to sum the range A2:An where n is the variable: Sum of A2:A4 =SUM(A2:INDEX(A:A,4)) Or: C1 = 4 =SUM(A2:INDEX(A:A,C1)) Note that if cell C1 is empty the *entire* range will be calculated. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Thanks for the response Valko. Actually I was looking to sum up specific number of cells by varying the row number or column alphabet. Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on another condition or sum rows A2:A7 based on a third condition. Hence I wanted to be able to vary the row number. Please advise if you think this is possible. Thanks! "T. Valko" wrote: Generally speaking... =INDEX(range,R,C) Whe R = row number C = column number Note that both R and C are *relative* to the range. For example: =INDEX(F5:J10,2,5) Returns the value at cell address J6. J6 is on the 2nd row in the 5th column relative to the range F5:J10. Both R and C can be calculated in many ways limited only by your imagination and/or skill level. You can also use this technique on a one dimensional array (single row or single column). =INDEX(A:A,10) Returns the value of cell A10 =INDEX(1:1,10) Returns the value of cell J1 You can use a range as an entire column (A:A) or row (1:1) as I did above or you can use a specific range. Just remember that R and C are *relative* to the range. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Example- Varying row number. If I need to look up cell A(x) where, A is the column alphabet. x is the variable row number. How can I input x as a variable? OR Example- Varying column alphabet. If I need to look up cell (x)1 where, x is the variable column alphabet. 1 is the row number. How can I input x as a variable? Thank you |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I vary the row number or column alphabet depending on
Hi Valko,
Thanks...this helps! The formula you wrote- =SUM(A2:INDEX(A:A,C1)) ....will certainly help me in the case of variable row number. To simplify my request could you advise on a similar formula which would represent a variable colum aplhabet? Thanks again! "T. Valko" wrote: Ok, you keep saying "based on a condition" but you don't say what that condition might be. So, to sum the range A2:An where n is the variable: Sum of A2:A4 =SUM(A2:INDEX(A:A,4)) Or: C1 = 4 =SUM(A2:INDEX(A:A,C1)) Note that if cell C1 is empty the *entire* range will be calculated. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Thanks for the response Valko. Actually I was looking to sum up specific number of cells by varying the row number or column alphabet. Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on another condition or sum rows A2:A7 based on a third condition. Hence I wanted to be able to vary the row number. Please advise if you think this is possible. Thanks! "T. Valko" wrote: Generally speaking... =INDEX(range,R,C) Whe R = row number C = column number Note that both R and C are *relative* to the range. For example: =INDEX(F5:J10,2,5) Returns the value at cell address J6. J6 is on the 2nd row in the 5th column relative to the range F5:J10. Both R and C can be calculated in many ways limited only by your imagination and/or skill level. You can also use this technique on a one dimensional array (single row or single column). =INDEX(A:A,10) Returns the value of cell A10 =INDEX(1:1,10) Returns the value of cell J1 You can use a range as an entire column (A:A) or row (1:1) as I did above or you can use a specific range. Just remember that R and C are *relative* to the range. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Example- Varying row number. If I need to look up cell A(x) where, A is the column alphabet. x is the variable row number. How can I input x as a variable? OR Example- Varying column alphabet. If I need to look up cell (x)1 where, x is the variable column alphabet. 1 is the row number. How can I input x as a variable? Thank you |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How can I vary the row number or column alphabet depending on
It would follow the same basic syntax:
To sum B1:D1 A3 = 4 =SUM(B1:INDEX(1:1,A3)) Excel doesn't evaluate the columns based on their letter heading. They get evaluated based on the column number. On the surface we see column A as "A" (in A1 reference style) but under the covers inside Excel see's it as column 1. We see column Z as column "Z". Excel see's it as column 26. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Hi Valko, Thanks...this helps! The formula you wrote- =SUM(A2:INDEX(A:A,C1)) ...will certainly help me in the case of variable row number. To simplify my request could you advise on a similar formula which would represent a variable colum aplhabet? Thanks again! "T. Valko" wrote: Ok, you keep saying "based on a condition" but you don't say what that condition might be. So, to sum the range A2:An where n is the variable: Sum of A2:A4 =SUM(A2:INDEX(A:A,4)) Or: C1 = 4 =SUM(A2:INDEX(A:A,C1)) Note that if cell C1 is empty the *entire* range will be calculated. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Thanks for the response Valko. Actually I was looking to sum up specific number of cells by varying the row number or column alphabet. Example- Sum rows A2:A4 based on a condition or sum rows A2:A6 based on another condition or sum rows A2:A7 based on a third condition. Hence I wanted to be able to vary the row number. Please advise if you think this is possible. Thanks! "T. Valko" wrote: Generally speaking... =INDEX(range,R,C) Whe R = row number C = column number Note that both R and C are *relative* to the range. For example: =INDEX(F5:J10,2,5) Returns the value at cell address J6. J6 is on the 2nd row in the 5th column relative to the range F5:J10. Both R and C can be calculated in many ways limited only by your imagination and/or skill level. You can also use this technique on a one dimensional array (single row or single column). =INDEX(A:A,10) Returns the value of cell A10 =INDEX(1:1,10) Returns the value of cell J1 You can use a range as an entire column (A:A) or row (1:1) as I did above or you can use a specific range. Just remember that R and C are *relative* to the range. -- Biff Microsoft Excel MVP "Tigerxxx" wrote in message ... Example- Varying row number. If I need to look up cell A(x) where, A is the column alphabet. x is the variable row number. How can I input x as a variable? OR Example- Varying column alphabet. If I need to look up cell (x)1 where, x is the variable column alphabet. 1 is the row number. How can I input x as a variable? Thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vary the vlookup array depending on the value in a cell | Excel Worksheet Functions | |||
| Vary column to sum in SumIf | Excel Worksheet Functions | |||
| How do I change the number To alphabet(EXAM: 10 TO ten) | Excel Discussion (Misc queries) | |||
| Vary the column widths for different rows | Excel Discussion (Misc queries) | |||
| sorting rows wich can vary in number | Excel Discussion (Misc queries) | |||
Hybrid Mode
