Complicated Formula

S

Stephen

Hi. I'm trying to do a very complicated formula.
I have a list of ten numbers in a row and I have one cell which is telling
me the max of those ten numbers.
This new cell I want to be able to find the number that was returned from
the above cell and then create a formula from that point. I want the formula
to keep looking back (going down the excel spreadsheet) looking for the
lowest number in a row from that number, and divide the current number by
that number.

Example:

From say a1:a10 I have :
2, 6, 4, 8, 4, 9, 3, 2, 1, 4

Cell one returns - 9
Cell two would look what came before the 9 and find the lowest number in a
row and divide cell one's number by this lower number. So it would find 1 is
the lowest number in a row and divide 9/1, bringing you to the answer of 9.
Thanks for you help!
 
B

Bernie Deitrick

Stephen,

=MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX(
A1:A10),A1:A10,FALSE)))

HTH,
Bernie
MS Excel MVP
 
B

Barb R.

Let me see if I understand what you want.

First, you say you have the data in cells A1-A10 and that data is in
columnar form, not a row is this correct?

Based on my read of this, I can interpret your question two different ways.

1) You want to divide the Maximum in the series by the Minimum in the series.
2) You want to divide the Maximum in the series, by the minimum of any data
listed after your maximum value.

Please advise.

Regards,
Barb Reinhardt
 
J

JulieD

Hi Stephen

cell 1
=MAX(A1:A10)

cell 2
=MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)))
or
=A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0)))
where A12 holds the formula of "cell 1" above
 
S

Stephen

Hi. I'm using the first formula you wrote under "cell two". It's working but
its grabbing the number above the column, not the numbers below. Do you know
how I could fix this?

Example :

1,3,5,2,4,

It's taking the 5 then dividing by the 1, not dividing by the 2 (on the
other side).
Thanks!


JulieD said:
Hi Stephen

cell 1
=MAX(A1:A10)

cell 2
=MAX(A1:A10)/MIN(OFFSET(A1:A10,0,0,MATCH(MAX(A1:A10),A1:A10,0)))
or
=A12/MIN(OFFSET(A1:A10,0,0,MATCH(A12,A1:A10,0)))
where A12 holds the formula of "cell 1" above
Click to expand...
 
S

Stephen

Hi.
I used your below formula and it ends up dividing by the lowest # in the
set. Not the lowest # in a row (below the highest) in the set.
Do you know how I could fix this?

Thanks!

Bernie Deitrick said:
Stephen,

=MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,FALSE)-1,0,11-MATCH(MAX(
A1:A10),A1:A10,FALSE)))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
B

Bernie Deitrick

Stephen,

I used my formula, and it worked, at least in the way that I thought you
wanted, dividing the max by the lowest number below - in a higher row
number, below the mxaimum on the screen, as your example showed. The only
thing that I can think of is that we are at cross-terms on our usage of
lower, and that you mixed up your example. I can send you a working
example, that does it both ways, if you would like. To contact me privately
take out the spaces and change the dot to .

HTH,
Bernie
MS Excel MVP


Stephen said:
Hi.
I used your below formula and it ends up dividing by the lowest # in the
set. Not the lowest # in a row (below the highest) in the set.
Do you know how I could fix this?

Thanks!
Click to expand...
 
B

Biff

Hi!

This works provided there will be no empty cells within the range:

=MAX(A1:A10)/MIN(OFFSET(A1,MATCH(MAX(A1:A10),A1:A10,0),,COUNT(A1:A10)-MATCH(MAX(A1:A10),A1:A10,0)))

Biff

Stephen said:
Hi. I'm using the first formula you wrote under "cell two". It's working
but
its grabbing the number above the column, not the numbers below. Do you
know
how I could fix this?

Example :

1,3,5,2,4,

It's taking the 5 then dividing by the 1, not dividing by the 2 (on the
other side).
Thanks!
Click to expand...
 
D

Domenic

Here's another way...

B1:

=MAX(A1:A10)

C1:

=B1/MIN(INDEX(A1:A10,MATCH(B1,A1:A10,0)+1):A10)

This will allow empty cells within the range. Note, the formula will
return a #DIV/0! error if zero is the lowest number in the relevant
range or no numbers exist within that range. The formula can be
modified to deal with these situations, if needed.

Hope this helps!
 
B

Biff

Hi!

You know, I was thinking after I posted that none of the replies so far
(including mine) has accounted for the fact that the max value may be the
last value in the range and would cause a return of #DIV/0!.

And then there's the possibility of dupe max's ......Which MIN do you want
after which dupe MAX .....

Biff
 
D

Domenic

Biff said:
You know, I was thinking after I posted that none of the replies so far
(including mine) has accounted for the fact that the max value may be the
last value in the range and would cause a return of #DIV/0!.
Click to expand...

It seems that in all cases, including mine, an error would be returned.
But I think that may be okay, since it would alert the user of that fact.
And then there's the possibility of dupe max's ......Which MIN do you want
after which dupe MAX .....
Click to expand...

Good question. I assumed, like everyone else, the minimum after the
first duplicate. :)
 
Top