Finding the largest number help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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)
 
That could return the incorrect date if another company has the same max
value and is listed before Company A.
 
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
 
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
 
Back
Top