Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to determine the number from given condition?

Does anyone have any suggestions on how to determine the number under
following conditions?
There is a list of number under column A & B, and I would like to determine
the highest values based on following conditions.
If any number under column A is smaller than the previous one from bottom to
top direction, then this is the starting point, such as row 5 is the starting
point, because in cell A6 is 6 and in cell A5 is 2. The other starting point
will be in cell A10 and cell A15.
Once the starting point is determine, then I would like to determine the
highest value from starting point to the top as long as the number in column
A is larger than previous one, such as
in cell A10, the highest number column B from row 10 to row 6 is 115.27 in
cell C10. The reason to select the number up to row 6 is that the number in
cell A6 is larger than the number in cell A5, therefore, this define as the
ending point. Therefore, I would like to determine the highest number within
the range from starting point to ending point. Another example will be from
row 15 to row 11, the highest number within this range is 114.65 in cell C15,
and another example will be from row 5 to row 1, the highest number within
this range is 114.54 in cell C5.

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to determine the number from given condition?

One possibility...

Insert a new row 1.

Enter 0 in C1

Enter this formula in C2 and copy down to C17:

=IF(A3<A2,"",MAX(B2:INDEX(B$1:B$17,MATCH(1000,C$1: C1)+1)))

Screencap:

http://img252.imageshack.us/img252/6775/max1lg7.jpg

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the number under
following conditions?
There is a list of number under column A & B, and I would like to
determine
the highest values based on following conditions.
If any number under column A is smaller than the previous one from bottom
to
top direction, then this is the starting point, such as row 5 is the
starting
point, because in cell A6 is 6 and in cell A5 is 2. The other starting
point
will be in cell A10 and cell A15.
Once the starting point is determine, then I would like to determine the
highest value from starting point to the top as long as the number in
column
A is larger than previous one, such as
in cell A10, the highest number column B from row 10 to row 6 is 115.27 in
cell C10. The reason to select the number up to row 6 is that the number
in
cell A6 is larger than the number in cell A5, therefore, this define as
the
ending point. Therefore, I would like to determine the highest number
within
the range from starting point to ending point. Another example will be
from
row 15 to row 11, the highest number within this range is 114.65 in cell
C15,
and another example will be from row 5 to row 1, the highest number within
this range is 114.54 in cell C5.

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to determine the number from given condition?

Thank you very much for your suggestions
There is one more question for determining the number

The starting points are on row 5, 10, 15
The ending points are on row 1, 6, 11
I would like to determine the number at the ending points,
110.9 will be returned in cell D5, because the number on row 1 is the ending
point.
114.74 will be returned in cell D10, because the number on row 6 is the
ending point.
114.01 will be returned in cell D15, because the number on row 11 is the
ending point.

Does you have any suggestions?
Thank you very much for any suggestions
Eric

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

"T. Valko" wrote:

One possibility...

Insert a new row 1.

Enter 0 in C1

Enter this formula in C2 and copy down to C17:

=IF(A3<A2,"",MAX(B2:INDEX(B$1:B$17,MATCH(1000,C$1: C1)+1)))

Screencap:

http://img252.imageshack.us/img252/6775/max1lg7.jpg

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the number under
following conditions?
There is a list of number under column A & B, and I would like to
determine
the highest values based on following conditions.
If any number under column A is smaller than the previous one from bottom
to
top direction, then this is the starting point, such as row 5 is the
starting
point, because in cell A6 is 6 and in cell A5 is 2. The other starting
point
will be in cell A10 and cell A15.
Once the starting point is determine, then I would like to determine the
highest value from starting point to the top as long as the number in
column
A is larger than previous one, such as
in cell A10, the highest number column B from row 10 to row 6 is 115.27 in
cell C10. The reason to select the number up to row 6 is that the number
in
cell A6 is larger than the number in cell A5, therefore, this define as
the
ending point. Therefore, I would like to determine the highest number
within
the range from starting point to ending point. Another example will be
from
row 15 to row 11, the highest number within this range is 114.65 in cell
C15,
and another example will be from row 5 to row 1, the highest number within
this range is 114.54 in cell C5.

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to determine the number from given condition?

If you followed my suggestion in the other post then everything has been
shifted down by 1 row.

Screencap:

http://img61.imageshack.us/img61/5549/eric1bv5.jpg

Formula entered in cell D2 then copied down to D17:

=IF(C2="","",INDEX(B$1:B$17,MATCH(1000,C$1:C1)+1))

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Thank you very much for your suggestions
There is one more question for determining the number

The starting points are on row 5, 10, 15
The ending points are on row 1, 6, 11
I would like to determine the number at the ending points,
110.9 will be returned in cell D5, because the number on row 1 is the
ending
point.
114.74 will be returned in cell D10, because the number on row 6 is the
ending point.
114.01 will be returned in cell D15, because the number on row 11 is the
ending point.

Does you have any suggestions?
Thank you very much for any suggestions
Eric

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

"T. Valko" wrote:

One possibility...

Insert a new row 1.

Enter 0 in C1

Enter this formula in C2 and copy down to C17:

=IF(A3<A2,"",MAX(B2:INDEX(B$1:B$17,MATCH(1000,C$1: C1)+1)))

Screencap:

http://img252.imageshack.us/img252/6775/max1lg7.jpg

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the number under
following conditions?
There is a list of number under column A & B, and I would like to
determine
the highest values based on following conditions.
If any number under column A is smaller than the previous one from
bottom
to
top direction, then this is the starting point, such as row 5 is the
starting
point, because in cell A6 is 6 and in cell A5 is 2. The other starting
point
will be in cell A10 and cell A15.
Once the starting point is determine, then I would like to determine
the
highest value from starting point to the top as long as the number in
column
A is larger than previous one, such as
in cell A10, the highest number column B from row 10 to row 6 is 115.27
in
cell C10. The reason to select the number up to row 6 is that the
number
in
cell A6 is larger than the number in cell A5, therefore, this define as
the
ending point. Therefore, I would like to determine the highest number
within
the range from starting point to ending point. Another example will be
from
row 15 to row 11, the highest number within this range is 114.65 in
cell
C15,
and another example will be from row 5 to row 1, the highest number
within
this range is 114.54 in cell C5.

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric






  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to determine the number from given condition?

Thank you very much for your suggestions
Eric


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to determine the number from given condition?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Thank you very much for your suggestions
Eric



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,670
Default How to determine the number from given condition?

I get one more question, if I would like to find the lowest value based on
the given condition, do you have any suggestions on how to modify the code? I
have tried to replace max with min, but it does not work.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

One possibility...

Insert a new row 1.

Enter 0 in C1

Enter this formula in C2 and copy down to C17:

=IF(A3<A2,"",MAX(B2:INDEX(B$1:B$17,MATCH(1000,C$1: C1)+1)))

Screencap:

http://img252.imageshack.us/img252/6775/max1lg7.jpg

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the number under
following conditions?
There is a list of number under column A & B, and I would like to
determine
the highest values based on following conditions.
If any number under column A is smaller than the previous one from bottom
to
top direction, then this is the starting point, such as row 5 is the
starting
point, because in cell A6 is 6 and in cell A5 is 2. The other starting
point
will be in cell A10 and cell A15.
Once the starting point is determine, then I would like to determine the
highest value from starting point to the top as long as the number in
column
A is larger than previous one, such as
in cell A10, the highest number column B from row 10 to row 6 is 115.27 in
cell C10. The reason to select the number up to row 6 is that the number
in
cell A6 is larger than the number in cell A5, therefore, this define as
the
ending point. Therefore, I would like to determine the highest number
within
the range from starting point to ending point. Another example will be
from
row 15 to row 11, the highest number within this range is 114.65 in cell
C15,
and another example will be from row 5 to row 1, the highest number within
this range is 114.54 in cell C5.

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default How to determine the number from given condition?

Here's a link to a sample file with all 3 formulas: Max, Min and Start:

http://cjoint.com/?lotkj4EknH

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
I get one more question, if I would like to find the lowest value based on
the given condition, do you have any suggestions on how to modify the
code? I
have tried to replace max with min, but it does not work.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

"T. Valko" wrote:

One possibility...

Insert a new row 1.

Enter 0 in C1

Enter this formula in C2 and copy down to C17:

=IF(A3<A2,"",MAX(B2:INDEX(B$1:B$17,MATCH(1000,C$1: C1)+1)))

Screencap:

http://img252.imageshack.us/img252/6775/max1lg7.jpg

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to determine the number under
following conditions?
There is a list of number under column A & B, and I would like to
determine
the highest values based on following conditions.
If any number under column A is smaller than the previous one from
bottom
to
top direction, then this is the starting point, such as row 5 is the
starting
point, because in cell A6 is 6 and in cell A5 is 2. The other starting
point
will be in cell A10 and cell A15.
Once the starting point is determine, then I would like to determine
the
highest value from starting point to the top as long as the number in
column
A is larger than previous one, such as
in cell A10, the highest number column B from row 10 to row 6 is 115.27
in
cell C10. The reason to select the number up to row 6 is that the
number
in
cell A6 is larger than the number in cell A5, therefore, this define as
the
ending point. Therefore, I would like to determine the highest number
within
the range from starting point to ending point. Another example will be
from
row 15 to row 11, the highest number within this range is 114.65 in
cell
C15,
and another example will be from row 5 to row 1, the highest number
within
this range is 114.54 in cell C5.

row 1 6 110.9
row 2 5 112.66
row 3 4 113.29
row 4 3 114.51
row 5 2 114.54
row 6 6 114.74
row 7 5 114.87
row 8 4 115.27
row 9 3 114.72
row 10 2 114.69
row 11 6 114.01
row 12 5 114.09
row 13 4 113.94
row 14 3 114.65
row 15 2 114.07
row 16 6 115.15

Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to determine the minimum value with given condition? Eric Excel Discussion (Misc queries) 2 August 15th 07 06:02 AM
Determine Row number JMay Excel Discussion (Misc queries) 3 July 3rd 07 01:40 PM
Determine number of rows meeting a condition Snapps Excel Discussion (Misc queries) 4 November 2nd 06 09:40 PM
How to determine the number in excel? Eric Excel Discussion (Misc queries) 1 March 13th 06 07:32 AM
How to determine the number of units based on given condition? Eric Excel Worksheet Functions 3 March 6th 06 10:22 AM


All times are GMT +1. The time now is 06:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"