Complicated Formula

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" <Stephen@discussions.microsoft.com> wrote in message
news:CD6B1F82-B59F-405A-A4F7-D314B81EB887@microsoft.com...
> 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" <Stephen@discussions.microsoft.com> wrote in message
> news:CD6B1F82-B59F-405A-A4F7-D314B81EB887@microsoft.com...
> > 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" <Stephen@discussions.microsoft.com> wrote in message
news:40717892-0481-4C29-A2AB-79725F6F184F@microsoft.com...
> 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" <Stephen@discussions.microsoft.com> wrote in message
> > news:CD6B1F82-B59F-405A-A4F7-D314B81EB887@microsoft.com...
> > > 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
> >
> >
> >