[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
st_moose
General User

Joined: 15 Apr 2009
Posts: 5

 Posted: Tue May 03, 2011 5:42 pm    Post subject: max/min from vlookup or sumproduct? Attempting to keep attendance for the MLB games and would like to have a cell that would give the max/min attendance for a given day of the week. Found sumproduct to get a total for the day, but can't work out how to get max/miin. Tried vlookup with error. columns are A: date B: day of week C: home/away D: opponent E: attendance Can anyone help? thanks
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2027
Location: Sydney, Australia

Posted: Tue May 03, 2011 10:22 pm    Post subject:

Perhaps these array formulae...
 Code: MAX(IF(\$B\$2:\$B\$1000=F1;\$E\$2:\$E\$1000;""))

 Code: MIN(IF(\$B\$2:\$B\$1000=F1;\$E\$2:\$E\$1000;""))

where the day of the week is in F1.
Array formula must be entered using the Ctrl+Shift+Enter PC key combination.
The Ctrl key must be held down while dragging the fill handle to fill an array formula in adjacent cells.

Ken Johnson
_________________
st_moose
General User

Joined: 15 Apr 2009
Posts: 5

 Posted: Wed May 04, 2011 5:17 pm    Post subject: Ctrl+Shift+Enter PC key combination? Please explain Tried =MIN(SUMPRODUCT(B2:B163="Monday";E2:E163;C2:C163="")) but that gives the result for the total, as I am guessing it should since SUMPRODUCT is adding all the Mondays? Is that correct? thanks
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2027
Location: Sydney, Australia

Posted: Wed May 04, 2011 7:34 pm    Post subject:

 st_moose wrote: Ctrl+Shift+Enter PC key combination? Please explain

On Personal Computers (PCs) there is the Control key, with caption "Ctrl"; the Shift key, with caption "Shift" and an up arrow; and the Enter key, with caption "Enter" and a left arrow.
The two formulae I suggested are not standard formulae, they are array formulae.
To enter a standard formula all you have to do is type it then press Enter (Return on an Apple computer).
To enter an array formula you have to hold down the Ctrl key (I think Command key on an Apple computer) and the Shift key while you press the Enter key.
When entered correctly Calc will, in the Input Line of the Formula Bar, enclose the array formula in braces...
 Code: {=MAX(IF(\$B\$2:\$B\$1000=F1;\$E\$2:\$E\$1000;""))}

You never add the braces yourself, that will have no effect.
The above formula is an array formula because the IF function's "Test" parameter is comparing an array of values, \$B\$2:\$B\$1000 with F1 so there has to be an array of test results. If the formula is not entered as an array formula it will only return the correct answer when it is entered into a cell that is on the same row as the column E cell holding the correct answer, otherwise it will return an incorrect answer or the #VALUE! error.

 st_moose wrote: Tried =MIN(SUMPRODUCT(B2:B163="Monday";E2:E163;C2:C163="")) but that gives the result for the total, as I am guessing it should since SUMPRODUCT is adding all the Mondays? Is that correct? thanks

Yes, that is correct. The SUMPRODUCT function returns a single value, not an array of values, so the MIN function can only return that same value.
The SUMPRODUCT function ia actually a special case array function because it is an array function that is entered using only the Enter key.

The array formula that will do what you are wanting is...
 Code: MIN(IF((B2:B163="Monday")*(C2:C163="");E2:E163;""))

I deliberately leave off the leading equals sign. If the formula was complete and if you copied it then pasted it into a Calc cell it would be automatically incorrect entered.
If you copy the incomplete array formula (sans leading equals sign) then paste it into a Calc cell then edit the cell to add the leading equals sign and finally press Ctrl+Shift+Enter, it will be correctly entered.

Ken Johnson
_________________
st_moose
General User

Joined: 15 Apr 2009
Posts: 5

 Posted: Fri May 06, 2011 5:46 pm    Post subject: SOLVED: max/min from vlookup or sumproduct? The 2nd formula MIN(IF((B2:B163="Monday")*(C2:C163="");E2:E163;"")) worked great!! Off to learn more about formulas that require the CTRL-ATL-ENTER entry!
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2027
Location: Sydney, Australia

Posted: Fri May 06, 2011 6:15 pm    Post subject: Re: SOLVED: max/min from vlookup or sumproduct?

Is that ALT bit correct?

Ken Johnson
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum