Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reference to a column of data?
Hi,
I need to do some data analysis on a set of data, basically to perform the MIN and MAX functions on a column of data, i.e., cell(F1) = MIN(D1:D100), cell(G1) = MAX(D1:D100), cell(F2) = MIN(D101:200), cell(G2) = MAX(D100:200), ....... cell(F30) = MIN(D3000:D3100), cell(G30) = MAX(D3000:3100) Something like that. I was wondering if I can reference these cells in a formula such as below: MIN(D$(100*i+1):D$((100+1)*i) for the i in 0,1,2, .... 30, where the i can be another column of data I can easily fill in incremental data. Thanks very much -- Jay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reference to a column of data?
You can use a formula like
=MAX(INDIRECT("A1:A"&C1)) This will give you MAX of A1:A20 if C1 contains 20. Basically build a string which evaluates to a range and pass it to INDIRECT function and wrap a MAX around it... "Jay" wrote: Hi, I need to do some data analysis on a set of data, basically to perform the MIN and MAX functions on a column of data, i.e., cell(F1) = MIN(D1:D100), cell(G1) = MAX(D1:D100), cell(F2) = MIN(D101:200), cell(G2) = MAX(D100:200), ....... cell(F30) = MIN(D3000:D3100), cell(G30) = MAX(D3000:3100) Something like that. I was wondering if I can reference these cells in a formula such as below: MIN(D$(100*i+1):D$((100+1)*i) for the i in 0,1,2, .... 30, where the i can be another column of data I can easily fill in incremental data. Thanks very much -- Jay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reference to a column of data?
Thank you so much Sheeloo! That worked.
One more question for you. If I want both of the markers, the A1 and A20 be indirect mapped to the values in C1 and D1 for instance, how would the expression be like, = MAX(INDIRECT("A:A"&C1,D1)) ? I'd really appreciate your help!!! Thanks again. -- Jay "Sheeloo" wrote: You can use a formula like =MAX(INDIRECT("A1:A"&C1)) This will give you MAX of A1:A20 if C1 contains 20. Basically build a string which evaluates to a range and pass it to INDIRECT function and wrap a MAX around it... "Jay" wrote: Hi, I need to do some data analysis on a set of data, basically to perform the MIN and MAX functions on a column of data, i.e., cell(F1) = MIN(D1:D100), cell(G1) = MAX(D1:D100), cell(F2) = MIN(D101:200), cell(G2) = MAX(D100:200), ....... cell(F30) = MIN(D3000:D3100), cell(G30) = MAX(D3000:3100) Something like that. I was wondering if I can reference these cells in a formula such as below: MIN(D$(100*i+1):D$((100+1)*i) for the i in 0,1,2, .... 30, where the i can be another column of data I can easily fill in incremental data. Thanks very much -- Jay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reference to a column of data?
You are most welcome...
Use =MAX(INDIRECT("A"&C1&":A"&D1)) The requirement is that the formula within INDIRECT() has to evaluate to the range you want... So "A"&C1&":A"&D1 you put all constants inside "" and append the cell reference with & "Jay" wrote: Thank you so much Sheeloo! That worked. One more question for you. If I want both of the markers, the A1 and A20 be indirect mapped to the values in C1 and D1 for instance, how would the expression be like, = MAX(INDIRECT("A:A"&C1,D1)) ? I'd really appreciate your help!!! Thanks again. -- Jay "Sheeloo" wrote: You can use a formula like =MAX(INDIRECT("A1:A"&C1)) This will give you MAX of A1:A20 if C1 contains 20. Basically build a string which evaluates to a range and pass it to INDIRECT function and wrap a MAX around it... "Jay" wrote: Hi, I need to do some data analysis on a set of data, basically to perform the MIN and MAX functions on a column of data, i.e., cell(F1) = MIN(D1:D100), cell(G1) = MAX(D1:D100), cell(F2) = MIN(D101:200), cell(G2) = MAX(D100:200), ....... cell(F30) = MIN(D3000:D3100), cell(G30) = MAX(D3000:3100) Something like that. I was wondering if I can reference these cells in a formula such as below: MIN(D$(100*i+1):D$((100+1)*i) for the i in 0,1,2, .... 30, where the i can be another column of data I can easily fill in incremental data. Thanks very much -- Jay |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to reference to a column of data?
What can I say, wonderful! All worked!
Thank you! -- Jay "Sheeloo" wrote: You are most welcome... Use =MAX(INDIRECT("A"&C1&":A"&D1)) The requirement is that the formula within INDIRECT() has to evaluate to the range you want... So "A"&C1&":A"&D1 you put all constants inside "" and append the cell reference with & "Jay" wrote: Thank you so much Sheeloo! That worked. One more question for you. If I want both of the markers, the A1 and A20 be indirect mapped to the values in C1 and D1 for instance, how would the expression be like, = MAX(INDIRECT("A:A"&C1,D1)) ? I'd really appreciate your help!!! Thanks again. -- Jay "Sheeloo" wrote: You can use a formula like =MAX(INDIRECT("A1:A"&C1)) This will give you MAX of A1:A20 if C1 contains 20. Basically build a string which evaluates to a range and pass it to INDIRECT function and wrap a MAX around it... "Jay" wrote: Hi, I need to do some data analysis on a set of data, basically to perform the MIN and MAX functions on a column of data, i.e., cell(F1) = MIN(D1:D100), cell(G1) = MAX(D1:D100), cell(F2) = MIN(D101:200), cell(G2) = MAX(D100:200), ....... cell(F30) = MIN(D3000:D3100), cell(G30) = MAX(D3000:3100) Something like that. I was wondering if I can reference these cells in a formula such as below: MIN(D$(100*i+1):D$((100+1)*i) for the i in 0,1,2, .... 30, where the i can be another column of data I can easily fill in incremental data. Thanks very much -- Jay |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating reference numbers from column of data | Excel Worksheet Functions | |||
Numeric reference of column. Data Grab ? | Excel Discussion (Misc queries) | |||
What is the cell data reference for 5th row and 7th column called | New Users to Excel | |||
Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) |