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

[Guest]

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!

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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

thanks

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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)

---