Complicated Formula #2

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
0
stephen (112)
4/15/2005 1:40:04 PM
excel.misc 78881 articles. 5 followers. Follow

12 Replies
588 Views

Similar Articles

[PageSpeed] 13

Hi

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

-- 
Andy.


"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 


0
Andy
4/15/2005 1:48:29 PM
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


0
Bernie
4/15/2005 1:53:57 PM
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
0
BarbR (262)
4/15/2005 1:54:04 PM
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 


0
JulieD1 (2295)
4/15/2005 1:57:09 PM
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 
> 
> 
> 
0
stephen (112)
4/15/2005 3:43:33 PM
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
> 
> 
> 
0
stephen (112)
4/15/2005 4:58:05 PM
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
> >
> >
> >


0
Bernie
4/15/2005 5:11:14 PM
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" <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
>>
>>
>> 


0
biffinpitt (3172)
4/16/2005 6:45:11 AM
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
> >>
> >>
> >>
0
domenic22 (716)
4/16/2005 11:12:40 AM
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" <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
>> >>
>> >>
>> >> 


0
biffinpitt (3172)
4/17/2005 4:04:00 AM
=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
>> >>
>> >>
>> >> 


0
biffinpitt (3172)
4/17/2005 4:11:44 AM
In article <#GY7#JwQFHA.4020@tk2msftngp13.phx.gbl>,
 "Biff" <biffinpitt@comcast.net> 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.  :)
0
domenic22 (716)
4/17/2005 12:15:39 PM
Reply:

Similar Artilces:

Locking a cell reference in a formula
I have a worksheet that has formulas built within 6 columns. I want to drag the formula in each column down 100 rows, but as I am calculating a date that references only 1 cell (A1) when I drag the formula, it picks up A2, A3, A4 etc. as the formula progresses down the column. Is there a way to lock the cell reference to A1 so that when I drag the formula down the column, that cell reference doesn't change? Add a $ sign in front of the cell reference. Example $A1 Locks the column but allows the rows to change A$1 locks the row but allows the column to change $A$1 loc...

Complicated pickle
I have a complicated (for me, anyway) problem that I have solved in a way that I'm not happy with. If anybody can offer suggestions for improvement to my methods, I'd very much appreciate it. The problem is that I've ended up with a set of formulas which fall apart if I insert a row. This is because I'm using the offset() function, where I suspect I should be using something else. The goal is to have 2 worksheets, named D (for data) and R (for report). The data is organized such that each column is a distinct entity. That is, the data points are rows and there can be hund...

2007 and 2 monitors
I have a question for you. I just received a new computer with Office 2007 and I cannot figure out how to do a screen print of a single screen with a dual monitor. In the prior version I would use Ctrl Alt Prt Sc - but it does not work with this new version. Do you by chance know how to do this? Have you tried the Snipping tool in Accessories? This is only available in Vista and Windows 7. -- Mary Sauer MVP http://msauer.mvps.org/ "Ron D" <Ron D@discussions.microsoft.com> wrote in message news:A29E99CD-59F8-46B6-BAEC-F42EB72F19FD@microsoft.com... >I h...

What VBA Stmt Will Tell Me the Index of a Chart's SERIES Formula?
I have written a macro that allows a user to select a specific series on an embedded chart, then specify a change to the series limit ranges. It works very well, EXCEPT I have not figured out how to write a statement that will return the index number of the selected series. I mistakenly believed that the series index number is the last number on the right end of the series formula. It's not. That's the plotting order sequence number, and it can change. I need the series index number, which I think is unique within the workbook. I tried using SELECTION.NAME after selecting the se...

Allow 2 or more referencial fields to same entity
When defining a custom entity it is currently not possible to define 2 separate relationships from this entity to another entity. For example, consider a custom "Project" Entity - it is not possible to create two relationships to Contact - one for a "Lead Engineer" and another for "Inspector". To do this, you would currently need to set up a many-to-many relationship using a 3rd entity and this makes things very difficult for the end user data entry, search and for reporting purposes. ---------------- This post is a suggestion for Microsoft, and Microsoft ...

Last cell in new formula
Can anyone please help? The code: Sub test() Dim x As Range Worksheets("sheet1").Activate Set x = Cells(Rows.Count, "N").End(xlUp) MsgBox x.Address End Sub I would like to find the last cell in column N and instead of the result being displayed in A msgbox, I would like to use the cell address in a formula after the code above e.g.: range("b2").formula = _ "sumproduct((N8:LASTCELLADDRESS>=0)*(N8:LASTCELLADDRESS<=1000)) Hi, Try this Dim LASTCELLADDRESS As Long LASTCELLADDRESS = Cells(Cells.Rows.Count, "...

Formula Problem #13
I seem to have trouble w/ a particular formula. I am attempting to round a number to the nearest tenth. No big deal...simple stuff if it were to stop there. I have rounded the number which returns as a decimal. I don't have trouble with any other number except when dealing with a zero in the 10ths spot. When I add text to the formula the decimal is not shown. Is there a way to have Excel first determine whether I have a zero in the tenths column and if so, have it show the decimal? Here's an example of the formula... FORMULA RESULT ...

password problem #2
hi... earlier tonight i was using outlook express 6 to check my mail.. when i went to write mail and send one my user name and password came up on the screen. i have been using this for months and never had a problem.. the username and password came up and i pressed ok.. and it keeps popping up.. but it is only for outgoing mail..now i think i did something to the password... how can i change it or whatever? thanks Rich nevermind i fixed it...... >-----Original Message----- >hi... earlier tonight i was using outlook express 6 to >check my mail.. when i went to write mail an...

Viewing formulas
I need to find a formula that's on the sheet I'm working on - I don't know what it is or where it is and it's causing problems. How can I display all formulas on the sheet I'm working on? -- Lynne Mawson Manchester Touching CNTRL ~ will toggle the display to shows formulas as opposed to values in the cells. -- Gary's Student "Lynne Mawson" wrote: > I need to find a formula that's on the sheet I'm working on - I don't know > what it is or where it is and it's causing problems. How can I display all > formulas on the sheet I...

Formulas do not refresh anymore
Has anynone encounter this problem in MS Excel in Office XP?? I use a spreadsheet that basically adds up numbers and gives me a tota on the bottom. Today it stopped doing that. I change numbers and th total stays unchanged unless I highlight the total's cell, put th cursor in the formula bar on top at the end of the formula and hi ENTER. It won't do it on its own anymore. This happens in ever spreadsheet I use so it's not just that one file. Can someone help? Thanks. Filipesk -- Message posted from http://www.ExcelForum.com Tools>Options>Calculation, check "Aut...

Complicated
In a sheet named "Confirmed" I have a spreadsheet which contains the following:- Column F (a list of languages) Column G (a list of languages) Column H (a fee per 1000 words) Column I (a fee per 1000 words) Now, I have set up a data validation series in a new sheet, so that when a language is selected, say for example, Arabic which is in F1 of the sheet "Confirmed", the languages listed next to it in G1 appears. If Arabic appears in F1, F2 and F3, then the languages which are listed in G1,G2 and G3 appear. Now what I want to do, is for in a new sheet in cell A1, I want t...

Urgently need help with creating formulas
Hello My worksheet is full of thousands of firstnames, middle names surnames, email addresses etc etc. How do I create a formula that wil result in having each persons *first name, initial of middle name surname and email address * ? I would really appreciate some help. Thank you. Ka -- Kat Hughe ----------------------------------------------------------------------- Kat Hughes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1674 View this thread: http://www.excelforum.com/showthread.php?threadid=31952 You didn't bother to tell us your setup so try thi...

Changing Inventory Item Types #2
In GP7.5, is there any way to change inventory item types once they have been used? Can the item be deleted and then recreated at the end of a year? Are there any utility programs that can do this? In particular, I've got some "sales inventory" items that I want to convert to "kits" and others that need to be "services" We are on 8.0, but I don't think it is possible to change the item type - not even with Professional System Tools. We have changed the item name (via tools) to include the word Discontinued. Then we set up a new item to repl...

How do i Select an A3 Publication to print on 2 A4's???
Hi, I was just wondering how i would tell Publisher that i want an A3 publication but i want it to print on 2 A4's. currently, instead of using two A4s which would be perfect size, its using 4 A4's and only printing in the middle orners of the pages. how would i change that so it only used 2 A4s Thanks. Elliot wrote: > I was just wondering how i would tell Publisher that i want an A3 > publication but i want it to print on 2 A4's. > > currently, instead of using two A4s which would be perfect size, its using 4 > A4's and only printing in the middle o...

Help me #2
Hi all, I have a MFC dll, in this dll I have a function. This is function's prototype: int GetCellAt(double left, double top, LPDISPATCH* lpCell). In this function I asign a valid pointer to lpCell. In the programe that uses this dll I call this function but after calling this function lpCell is always NULL. When I change the function declaration like this : LPDISPATCH GetCellAt(double,double) and in this function I return the pointer that I asigned to lpCell. After calling this function I get an valid pointer. Please tell me why? In your first function, you have declared a &q...

What is correct formula?
Can someone please explain me what is correct formula i excel.....I need to markup or increase A1 for 30% A1=16101.35 =A1*1.3 and that is 20931.75 (B1) when I go to check =1-(A1/B1) i get 23% -------------------------------------------------------- A1=16101.35 =A1/0.7 and that is 23001.93 (B1) when I go to check =1-(A1/B1) i get 30% So what is correct? Hi dmmatic, 16101.35*1.3=20931.75 and 16101.35/0.7=23001.93 so the problem is with your checking. If you use a formula like =(B1-A1)/A1, that will give you 0.3 (or 30%) for the first calculation, and 0.42857... (or 42.857%) for the secon...

Subtotaling with formulas?
I have a large sheet, so I need some automated method to subtotal it. There are a few different GFS CODES in one column and many different BRANCH CODES in another. At the bottom of the sheet is a table of various COUNTIF calculations on the GFS CODES. What I do right now is autofilter the BRANCH CODES and load each branch individually. I then update the named range to only the GFS CODES in that particular branch, and finally print out the page with the updated COUNTIF calculations. The SUBTOTAL tool can do one COUNTIF calculation, but is there a way I can have a "table of GFS CODE COUNTI...

What does Microsoft Project Levels 1, 2, 3, 4, schedules mean?
I'm a MSP new user and I want a detailed explanation to the following questions: What is MS Project 'Level 1' schedule? What is MS Project 'Level 2' schedule? What is MS Project 'Level 3' schedule? and What is MS Project 'Level 4' schedule? AFAIK, I never heard of such nomenclature in the context of the product as provided by Microsoft. Are these words used on your project? Where did you see these words written, or who uses them? --rms www.rmschneider.com On 03/01/10 07:30, Alfred wrote: > I'm a MSP new user and I want ...

football formula Fixture table help
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C5B13F.18EB58A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all Looking for a formula to locate a score from a results table place it = next to the fixture list. (backward sounding I know but from fixture list I can ascertain current = form, but I cant from a table) Created a fixture list I.e. Arsenal vs. Aston Villa is the first = fixture. and I want to pull the result for each team from the table of = results.=20 Results Table Sheet a bc...

office:mac #2
I just purchased office:mac 2004 and was wondering what the seal is supposed to look like. Mine looks like someone left off a small piece of the seal in the middle with a silver line through it and red writing on the silver line. Does that make any since? The guy at Staples said it is supposed to look like that. Help.....thanks! On 17/10/07 11:14 AM, in article 1192583675.602242.208900@z24g2000prh.googlegroups.com, "sstewart@mcleodusa.net" <sstewart@mcleodusa.net> wrote: > I just purchased office:mac 2004 and was wondering what the seal is > supposed to look like. ...

link formula to sheet.
Hi all I need a formula that will link to the same cell in different worksheets. for example a1 will be c2 in worksheet 1. b2 will be c2 in worksheet 2 etc....... thanks for you help in advance. Hi try ='sheet2'!C2 -- Regards Frank Kabel Frankfurt, Germany "Tom" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:3aa701c4ab13$a8064cd0$a301280a@phx.gbl... > Hi all > > I need a formula that will link to the same cell in > different worksheets. for example a1 will be c2 in > worksheet 1. b2 will be c2 in worksheet 2 etc....... > >...

send as public folder permissions #2
I am trying to give a user rights to send on behalf of the email folder. I went to Exchange Admin properties of the folder and under permission "directory rights" and "administrative rights" I've added the user and given all rights including "send as Replicated all CD and login again but still getting an error "you do not have permission to send on behalf any help will be greatly appreciated Thank you L ...

PST files #2
Is there a size limit for pst files? Depends. In versions of Outlook prior to 2003 the limit was 2GB. Outlook 2003 can create Unicode PST files, however, which can grow quite a lot larger if necessary. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "John" <anonymous@discussions.microsoft.com> wrote in message news:043201c3a95e$c363e670$a301280a@phx.gbl... > Is there a size limit for pst files? ...

Formula to Text to Formula
Hi there! Using E02 on XP. Have zillions of formulas to create and if I can get my template going, it will be much easier. Only problem is this: I used Replace to change all my ='1'!'s with '=1'1! so I can see my formulas and use copy/replace to create my boiler templates on multiple worksheets. I have done this in the past and then just replace in reverse and voila! I have formulas linked to the correct worksheet locations. This time I keep getting the Excel can't find anything to replace, check your formating, etc. What am I doing wrong? I really do not want...

asp.net page with framework 2.0 andajax
I initially made an ASP.NET page and after I added AJAX code by entering the page is the initial declaration <% @ Register Assembly = "System.Web.Extensions, Version = 1.0.61025.0, Culture = neutral, PublicKeyToken = 31bf3856ad364e35 " Namespace = "System.Web.UI" TagPrefix = "asp"%> then I have write the ScriptManager in this page. In the web.config page i have write the tag <add assembly = "System.Web.Extensions, Version = 1.0.61025.0, Culture = neutral, PublicKeyToken = 31bf3856ad364e35 "/></ assemblies>. Now w...