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

I like that one!

Biff

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-F7C89A.07124016042005@msnews.microsoft.com...
> 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
>> >>
>> >>
>> >>