Selective Maximums

D

dranon

I've been banging my head against the wall on this and can't seem to
snuff out the right combination. If anybody can help, I would be most
appreciative.

I have a series of numbers in columns 1 through 10.

I want to have another 10 columns that list the running maximums, with
a twist.

In column 11, I will repeat column 1.
In column 12, I want the maximum of column 1 or column 2.
In column 13, I want the maximum of columns 1, 2 or 3.
etc.
In column 20, I want the maximum of columns 1 through 10.

With one twist.

I only want to use the number from the first 10 columns if a
**corresponding** number (in a helper column) satisfies a specific
criteria.

For this purpose, I have 10 helper columns, 21 through 30.

Let's assume that my criteria (applied to columns 21 through 30) is
that the number must be 1000 or more.

For example, in column 15, I would want:

=Max(if(column 21>=1000,column 1,0),
if(column 22>=1000,column 2,0),
if(column 23>=1000,column 3,0),
if(column 24>=1000,column 4,0),
if(column 25>=1000,column 5,0))

I have left off the row identifiers since the information is
irrelevant.

If anybody can point me in the right direction I would be much
obliged.

Thanks
 
C

Charabeuh

Hello,

I'm not sure to understand what you want to do.

I assumed your data are in A1:J10.
I assumed your criteria are in U1:AD10

Into K1 enter the array formula:
=MAX(IF($U1:U1>1000,$A1:A1,0))
This formula should be validate with the combination of the three keys
Ctrl+Shift+Enter instead with the single key Enter

Copy this formula to the others cells (L1:T1 and K2:T10)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top