Complicated Formula
 

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!
--
Thanks!

Stephen

Hi

Try:
=MAX(A1:A10)/MIN(A1:A10)

--
Andy.


"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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


"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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

"Stephen" wrote:

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!
--
Thanks!

Stephen

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stephen" wrote in message
...
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!
--
Thanks!

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" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stephen" wrote in message
...
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!
--
Thanks!

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" wrote:

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


"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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" wrote in message
...
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" wrote:

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


"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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(A 1:A10)-MATCH(MAX(A1:A10),A1:A10,0)))

Biff

"Stephen" wrote in message
...
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" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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!

In article ,
"Biff" wrote:

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(A 1:A10)-MATCH(MA
X(A1:A10),A1:A10,0)))

Biff

"Stephen" wrote in message
...
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" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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

"Domenic" wrote in message
...
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!

In article ,
"Biff" wrote:

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(A 1:A10)-MATCH(MA
X(A1:A10),A1:A10,0)))

Biff

"Stephen" wrote in message
...
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" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

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

I like that one!

Biff

"Domenic" wrote in message
...
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!

In article ,
"Biff" wrote:

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(A 1:A10)-MATCH(MA
X(A1:A10),A1:A10,0)))

Biff

"Stephen" wrote in message
...
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" wrote:

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stephen" wrote in message
...
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!
--
Thanks!

Stephen

In article ,
"Biff" wrote:

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!.

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 .....

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