OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

max/min from vlookup or sumproduct?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
st_moose
General User
General User


Joined: 15 Apr 2009
Posts: 5

PostPosted: Tue May 03, 2011 5:42 pm    Post subject: max/min from vlookup or sumproduct? Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Tue May 03, 2011 10:22 pm    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
st_moose
General User
General User


Joined: 15 Apr 2009
Posts: 5

PostPosted: Wed May 04, 2011 5:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Wed May 04, 2011 7:34 pm    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
st_moose
General User
General User


Joined: 15 Apr 2009
Posts: 5

PostPosted: Fri May 06, 2011 5:46 pm    Post subject: SOLVED: max/min from vlookup or sumproduct? Reply with quote

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! Smile
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

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

st_moose wrote:
Off to learn more about formulas that require the CTRL-ATL-ENTER entry!

Is that ALT bit correct?

Ken Johnson
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group