Max Value Query

[Chris26]

Hi

Below is a Small extract of my data.

Column A ----- ------Column B

20/7/2002 10:00:00 ----- 0
20/7/2002 10:15:00 ----- 2
20/7/2002 10:30:00 ----- 6
20/7/2002 10:45:00 ----- 4
20/7/2002 11:00:00 ----- 0

I would like Cell C1 to return the Date/Time in Column A that corresponds to the Max Value in Col B. (i.e. C1=20/7/2002 10:30:00)
Sometimes the Max Value will occur on more than one Date, is it possible to list all occurances ?

Many Thanks in advance
Chris

 

[Aladin Akyurek]

Hi

Below is a Small extract of my data.

Column A ----- ------Column B

20/7/2002 10:00:00 ----- 0
20/7/2002 10:15:00 ----- 2
20/7/2002 10:30:00 ----- 6
20/7/2002 10:45:00 ----- 4
20/7/2002 11:00:00 ----- 0

I would like Cell C1 to return the Date/Time in Column A that corresponds to the Max Value in Col B. (i.e. C1=20/7/2002 10:30:00)
Sometimes the Max Value will occur on more than one Date, is it possible to list all occurances ?

Many Thanks in advance
Chris

E2:

=MAX(B2:B6)

E3:

=COUNTIF(B2:B6,E2)

E5, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$5:E5)<=$E$3,INDEX($A$2:$A$6,SMALL(IF($B$2:$B$6=$E$2,
    ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($E$5:E5))),"")

 

[T. Valko]

Hi

Below is a Small extract of my data.

Column A ----- ------Column B

20/7/2002 10:00:00 ----- 0
20/7/2002 10:15:00 ----- 2
20/7/2002 10:30:00 ----- 6
20/7/2002 10:45:00 ----- 4
20/7/2002 11:00:00 ----- 0

I would like Cell C1 to return the Date/Time in Column A that corresponds to the Max Value in Col B. (i.e. C1=20/7/2002 10:30:00)
Sometimes the Max Value will occur on more than one Date, is it possible to list all occurances ?

Many Thanks in advance
Chris

One way...

Let's assume your data is in the range A2:B6.

In the formulas I use the following named ranges:

• Dates
• Refers to: =$A:$A
• Values
• Refers to: =$B$2:$B$6

Enter this formula in D2. This will return the max value from the Values range.

=MAX(Values)

Enter this formula in E2. This will return the count of instances of the max value.

=COUNTIF(Values,D2)

Enter this array formula** in F2 and copy down until you get blanks. This will extract the dates/times.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(Dates,SMALL(IF(Values=D$2,ROW(Values)),ROWS(F$2:F2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[drewcarpenter]

I have a project tasklist contained in a workbook, where the summary sheet is named "Master", and subsequent tabs are named for responsible parties, X, XX, and XXX. Tabs are bracketed by hidden sheets ("First" and "Last"). Each task has a unique identifying number in Column B and, in column I, each responsible party will be able to update their task completion %.

What I need is a formula on the master sheet that extracts the max value of all entries for a given task (each task will be in a different row depending on whose sheet it appears upon).

For Row2 of Master spreadsheet, my formula currently is: =SUMPRODUCT((THREED(First:Last!B$1:B$600)=B2)+0,(THREED(First:Last!I$1:I$600)))

The problem is that this sums all %s entered, instead of selecting the largest, but I am having a hard time trying to use MAX to extract the values... help????

 

[T. Valko]

I have a project tasklist contained in a workbook, where the summary sheet is named "Master", and subsequent tabs are named for responsible parties, X, XX, and XXX. Tabs are bracketed by hidden sheets ("First" and "Last"). Each task has a unique identifying number in Column B and, in column I, each responsible party will be able to update their task completion %.

What I need is a formula on the master sheet that extracts the max value of all entries for a given task (each task will be in a different row depending on whose sheet it appears upon).

For Row2 of Master spreadsheet, my formula currently is: =SUMPRODUCT((THREED(First:Last!B$1:B$600)=B2)+0,(THREED(First:Last!I$1:I$600)))

The problem is that this sums all %s entered, instead of selecting the largest, but I am having a hard time trying to use MAX to extract the values... help????

You should start a new thread for this.

 

[Chris26]

Thanks for taking the time to help.
I got the answer I was looking for.

Much Appreciated

 

[Aladin Akyurek]

Thanks for taking the time to help.
I got the answer I was looking for.

Much Appreciated

That's great. Thanks for providing feedback.

 

[T. Valko]

Thanks for taking the time to help.
I got the answer I was looking for.

Much Appreciated

You're welcome. Thanks for the feedback!