Can I use cell contents as part of a formula?

[Brian Rhodes]

For example:

Column C has a bunch of numerical values for which I'd like to get the MIN,
MAX, Average, etc of. But, I don't always want to do the entire column.
There are "chunks" of data that I'd like to perform the operations on.

So, I figure out the chunks automatically and place them in Column N. So,
Column N looks like this:

N1=C1
N2=C8
N3=C2350
N4=C3458

What'd I'd like to do is this:

Min(C1:C8)
Min(C8:C2350)
Min(C2350:C3458)

But, I'd like to use the values that are in the "N" cells, as I have many
worksheets that I'm analyzing data in and I'd like to just copy and paste
the formulas from the N column and not have to figure out where the
sub-chunks of data are located.

Thanks
-Brian

 

[Guest]

As a for instance:

=MIN(INDIRECT(N1&":"&N2))

which calculates as MIN(C1:C8)

 

[JE McGimpsey]

Check out INDIRECT() in XL Help. For instance:

=MIN(INDIRECT(N1&":"&N2))

 

[Guest]

The offset function will probably work for you. (check help on offset)

you would not need to put the C in your N cells

for example the equivelent ot min(c1:C8) would be

if n1=1,n2=8, n3=2350
=min(offset(c1,n1-1,0,n2-n1+1,1))
for min(C8:C2350)
=min(offset(c1,n2-1,n3-n2+1,1))



if you need the full C1,C8,C2350 etc for other purposes

=min(offset(c1,value(right(n1,len(n1)-1)-1,0,value(right(n2,len(n2)-1)-right(n1,len(n1)-1)+1,1))
for min(C8:C2350
=min(offset(c1,right(n2,len(n2)-1)-1,right(n3,len(n3)-1)-right(n2,len(n2)-1)+1,1))