Finding the largest number help

[John]

I have an excel spreadsheet with four columns. I have one work sheet that
needs to findest
the highest dollar amount during a period of time.

Example

Company Date Total Credits Total Debits

Company A 10/1/07 125,000 200,000
Company A 10/2/07 75,500 125,000
Company B 10/2/07 500,000 1,000
Company A 10/3/07 400,000 100,000
Company B 10/5/07 200,000 200,000


So the worksheet would first need to look for all the ones that have company
A and then look for the highest number. So in this case it would have
happened on 10/3/2007 (15). I would think this would involve a vlookup, but
can't figure how to write it. Could someone lend some insight. Anything
will help
Thanks

 

[RagDyer]

In your explanation, what does the "(15)" after the 10/3/2007 designate?

Anyway, try this *array* formula:

=INDEX(B3:B7,MATCH(MAX((A3:A7="Company A")*C3:C7),C3:C7,0))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

Format the cell containing the formula to Dates.

 

[T. Valko]

It's not clear what you want.

Do you want the highest number for Company A, and if so, from which column?

Or, do you want the date on which Company A had the highest number, and if
so, from which column?

Here's how interpreted your question: return the date on which Company A had
the highest CREDIT:

Array entered** :

=INDEX(B2:B6,MATCH(1,(A2:A6="Company A")*(C2:C6=MAX(IF(A2:A6="Company
A",C2:C6))),0))

Format as DATE

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

That could return the incorrect date if another company has the same max
value and is listed before Company A.

 

[RagDyer]

You're right.
Didn't check for that.

 

[Sandy Mann]

RagDyer,

Would modifying your formula to:

=INDEX(B3:B7,MATCH(MAX((A3:A7="Company A")*C3:C7),(A3:A7="Company
A")*C3:C7,0))

do the job? It seems t for the testing that I have tried.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Yeah, that'll work.

 

[Sandy Mann]

Thanks for checking it out Biff, I didn't have enough confidence to declare
it as an answer myself.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk