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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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(A1:A10)-MATCH(MAX(A1:A10),A1:A10,0)))

Biff

"Stephen" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 <[email protected]>,
"Biff" <[email protected]> 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(A1:A10)-MATCH(MA
> X(A1:A10),A1:A10,0)))
>
> Biff
>
> "Stephen" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Biff" <[email protected]> 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(A1:A10)-MATCH(MA
>> X(A1:A10),A1:A10,0)))
>>
>> Biff
>>
>> "Stephen" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "Biff" <[email protected]> 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(A1:A10)-MATCH(MA
>> X(A1:A10),A1:A10,0)))
>>
>> Biff
>>
>> "Stephen" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 <#GY7#[email protected]>,
"Biff" <[email protected]> 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.