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

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

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

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

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