Need MAX value if condition is met
 

I have a spreadsheet that lists usage in our computer lab. From the data I
am able to get the average usage for each day using the averageif function
=AVERAGEIF(Adjusted!$B$3:$B$25,"Thu",Adjusted!$I$3 :$I$25). How can I get the
max value for each day. There is no maxif or 'if.. then' functions.

Need MAX value if condition is met
 

=MAX(IF(Adjusted!$B$3:$B$25,"Thu",Adjusted!$I$3:$I $25))
Confirm formula as an array formula by using Ctrl+Shift+enter

I'm assuming the B column is your days, and I column is numbers. If not,
switch the arrays around.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SCC" wrote:

I have a spreadsheet that lists usage in our computer lab. From the data I
am able to get the average usage for each day using the averageif function
=AVERAGEIF(Adjusted!$B$3:$B$25,"Thu",Adjusted!$I$3 :$I$25). How can I get the
max value for each day. There is no maxif or 'if.. then' functions.

Need MAX value if condition is met
 

Try this array formula** :

=MX(IF(Adjusted!$B$3:$B$25="Thu",Adjusted!$I$3:$I$ 25))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"SCC" wrote in message
...
I have a spreadsheet that lists usage in our computer lab. From the data I
am able to get the average usage for each day using the averageif function
=AVERAGEIF(Adjusted!$B$3:$B$25,"Thu",Adjusted!$I$3 :$I$25). How can I get
the
max value for each day. There is no maxif or 'if.. then' functions.

Need MAX value if condition is met
 

Thanks for the hint. It looks like this is the formula
{=MAX((Adjusted!$B$3:$B$132="Fri")*(Adjusted!$E$3: $E$132))}

"T. Valko" wrote:

Try this array formula** :

=MX(IF(Adjusted!$B$3:$B$25="Thu",Adjusted!$I$3:$I$ 25))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"SCC" wrote in message
...
I have a spreadsheet that lists usage in our computer lab. From the data I
am able to get the average usage for each day using the averageif function
=AVERAGEIF(Adjusted!$B$3:$B$25,"Thu",Adjusted!$I$3 :$I$25). How can I get
the
max value for each day. There is no maxif or 'if.. then' functions.

Need MAX value if condition is met
 

That's another way to write it.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SCC" wrote in message
...
Thanks for the hint. It looks like this is the formula
{=MAX((Adjusted!$B$3:$B$132="Fri")*(Adjusted!$E$3: $E$132))}

"T. Valko" wrote:

Try this array formula** :

=MX(IF(Adjusted!$B$3:$B$25="Thu",Adjusted!$I$3:$I$ 25))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"SCC" wrote in message
...
I have a spreadsheet that lists usage in our computer lab. From the
data I
am able to get the average usage for each day using the averageif
function
=AVERAGEIF(Adjusted!$B$3:$B$25,"Thu",Adjusted!$I$3 :$I$25). How can I
get
the
max value for each day. There is no maxif or 'if.. then' functions.