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 <ue28Y$kQFHA.2748@TK2MSFTNGP09.phx.gbl>,
"Biff" <biffinpitt@comcast.net> 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" <Stephen@discussions.microsoft.com> wrote in message
> news:38E05CC1-B109-4871-B4FD-52F1D3A8FA21@microsoft.com...
> > 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" <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
> >>
> >>
> >>