| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding the largest number help
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the largest number help
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "John" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the largest number help
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) -- Biff Microsoft Excel MVP "John" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the largest number help
That could return the incorrect date if another company has the same max
value and is listed before Company A. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "John" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the largest number help
You're right.
Didn't check for that. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... That could return the incorrect date if another company has the same max value and is listed before Company A. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "John" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the largest number help
Yeah, that'll work.
-- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "RagDyer" wrote in message ... You're right. Didn't check for that. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... That could return the incorrect date if another company has the same max value and is listed before Company A. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "John" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding the largest number help
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 Replace @mailinator.com with @tiscali.co.uk "T. Valko" wrote in message ... Yeah, that'll work. -- Biff Microsoft Excel MVP "Sandy Mann" wrote in message ... 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 Replace @mailinator.com with @tiscali.co.uk "RagDyer" wrote in message ... You're right. Didn't check for that. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "T. Valko" wrote in message ... That could return the incorrect date if another company has the same max value and is listed before Company A. -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "John" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Finding largest number from list | Excel Discussion (Misc queries) | |||
| Finding the Largest Number, based on two criteria | Excel Discussion (Misc queries) | |||
| Finding the 3rd largest number in a list | Excel Worksheet Functions | |||
| finding the largest number from the last row and look up its name. | Excel Worksheet Functions | |||
| finding the second largest number in a list | Excel Discussion (Misc queries) | |||
Linear Mode
