Display Time of Result from Max Formula in New Cell

[dlaymusic]

Hopefully someone out there can help me with this; it's been years since I touched Excel.

It may be important to note that I'm doing most of the creation of this spreadsheet on Office 2010 (on my home computer) but I need to make the spreadsheet compatible with Excel 2003 (on my computer and the computers of others who will use this sheet - we're begging for an upgrade!!!) .

I have a formula in Sheet 1 B2. It pulls the maximum percentage from Sheet 2 D2:D4 (contact %) if the value in Sheet 2 B2:B4 (total calls) is greater than 2. What I need in Sheet 1 A2 is a formula which tells the corresponding time to the result of the formula in Sheet 1 B2 (the time is found in Sheet 2 A2:A4). So, in the table below, the missing formula (Sheet 1 A2) should show 1:00 PM, as the contact % is 50% and total calls is greater than 2. Though Sheet 2 D4 shows 50% as well, only 2 calls were made, therefore it should be omitted from consideration from Sheet 1 A2.

The tables can be found here:

http://www.evernote.com/shard/s51/s...ec550708766a/4e2ebc5ba3839d04d9fd7ea7082e7218

• The formula in B2 is =MAX(IF(Sheet2!C2:C4>2,Sheet2!D2:D4)); the result is formatted as a percentage w/ 2 decimal places.
• The formula in D2:D4 is =C*/B*
• Column A is formatted as time (*:** AM/PM)
• Columns B and C are formatted as numbers, no (.)
• Column D is formatted as a % with 2 decimal places.

 

[Oaktree]

Welcome to the board!

Try:

=INDEX(Sheet2!A2:A4,MAX(IF(Sheet2!C2:C4>2,IF(Sheet2!D2:D4=B2,ROW(Sheet2!D2:D4)-ROW(Sheet2!D1)))))

also confirmed with CTRL+SHIFT+ENTER

 

[dlaymusic]

Thanks for the help! New problems bubbling to the surface already.