MAX figure within a date range as a function of today()'s date
 

Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily production
figure for the month by referencing today's date to find which month to
consider.

I am using a sumif formula to calculate the month's cumulative production
figure but haven't figured out how to find the highest daily figure of the
month.

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.

Thanks!

MAX figure within a date range as a function of today()'s date
 

Try:

=MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))

This ia an array formula so enter it with Ctrl + Shift + Enter not just
enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily production
figure for the month by referencing today's date to find which month to
consider.

I am using a sumif formula to calculate the month's cumulative production
figure but haven't figured out how to find the highest daily figure of the
month.

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.

Thanks!

MAX figure within a date range as a function of today()'s date
 

Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
accomplish?

thanks

"Sandy Mann" wrote:

Try:

=MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))

This ia an array formula so enter it with Ctrl + Shift + Enter not just
enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily production
figure for the month by referencing today's date to find which month to
consider.

I am using a sumif formula to calculate the month's cumulative production
figure but haven't figured out how to find the highest daily figure of the
month.

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.

Thanks!

MAX figure within a date range as a function of today()'s date
 

"irvine79" wrote in message
...
Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
accomplish?


It makes Excel work on each elemant of the first array with the first
element of the second array. ie:

=SUM((A1:A3)*(B1:B3))

work out as

=SUM(A1*B1,A2*B2,A3*B3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
accomplish?

thanks

"Sandy Mann" wrote:

Try:

=MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))

This ia an array formula so enter it with Ctrl + Shift + Enter not just
enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily
production
figure for the month by referencing today's date to find which month to
consider.

I am using a sumif formula to calculate the month's cumulative
production
figure but haven't figured out how to find the highest daily figure of
the
month.

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.

Thanks!

MAX figure within a date range as a function of today()'s date
 

This is part of what I was looking for but need one more step. I have a huge
list of participants in a study. I wanted to look through this huge list and
find the max of weight loss among Men. I have a variable M/F so I can find
the max number but what I would llike is a function that will also give me
the person's name associated with this max number - in a different column of
same sheet. Is this possible? I appreciate anyhelp you guys can give.

"Sandy Mann" wrote:

"irvine79" wrote in message
...
Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
accomplish?


It makes Excel work on each elemant of the first array with the first
element of the second array. ie:

=SUM((A1:A3)*(B1:B3))

work out as

=SUM(A1*B1,A2*B2,A3*B3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Thanks, worked perfect! One more question, what does Ctrl + Shift + Enter
accomplish?

thanks

"Sandy Mann" wrote:

Try:

=MAX((MONTH(B1:B1000)=MONTH(TODAY()))*(H1:H1000))

This ia an array formula so enter it with Ctrl + Shift + Enter not just
enter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"irvine79" wrote in message
...
Column B = All dates for this year.
Column H = production figures entered daily.

My goal is to come up with a cell that calculates the max daily
production
figure for the month by referencing today's date to find which month to
consider.

I am using a sumif formula to calculate the month's cumulative
production
figure but haven't figured out how to find the highest daily figure of
the
month.

=SUMIF(B3:B262,TEXT(TODAY(),"mmm"),H3:H262) ...

Basically in need a MAX version of the above formula.

Thanks!

MAX figure within a date range as a function of today()'s date
 

One way ..

Assuning names in col A, gender (M/F) in col B, weight loss in col C
(expressed as positive numbers), all data within rows 2 to 1000 (say)

Array-entered (press CTRL+SHIFT+ENTER) in say D2:
=MAX(IF(B2:B1000="M",C2:C1000))
will return the max weight loss for Males
(assumes no ties in the max weight loss)

Then in say, E2:
=INDEX(A2:A1000,MATCH(D2,C2:C1000,0))
will return the corresponding name from col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JICDB" wrote:
This is part of what I was looking for but need one more step. I have a huge
list of participants in a study. I wanted to look through this huge list and
find the max of weight loss among Men. I have a variable M/F so I can find
the max number but what I would llike is a function that will also give me
the person's name associated with this max number - in a different column of
same sheet. Is this possible? I appreciate anyhelp you guys can give.

MAX figure within a date range as a function of today()'s date
 

Oops, correction ..
Then in say, E2:
=INDEX(A2:A1000,MATCH(D2,C2:C1000,0))

Put instead in E2, array-entered (CTRL+SHIFT+ENTER):
=INDEX(A2:A1000,MATCH(D2,IF(B2:B1000="M",C2:C1000) ,0))
to return the corresponding name from col A

And if the weight loss in col C is expressed as negative numbers
just change the array formula in D2 to:
=MIN(IF(B2:B1000="M",C2:C1000))
(Use the same formula for E2)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---