Can this be done

[drose]

Hi,
The formula below looks at current month, it their a way to look at previous
month?
=MAX(IF(MONTH(A2:A11)=MONTH(NOW()),B2:B11))

 

[FSt1]

hi
just subtract 1 from MONTH(NOW())

=MAX(IF(MONTH(A2:A11)=MONTH(NOW())-1,B2:B11))

and i might use Today() instead of now() becasue now() has the time
attached to where today() doesn't. month() will strip the time off but if all
you are after is the month, then that may be just less clutter for excel to
trip over. i don't think it would make any profound or noticeable difference
but, hey, a nanosecond here, a couple of bytes there. might add up eventually.

Regards
FSt1

 

[Joel]

One way is toi get the 1st day of the current month and then subtract 1 which
will get the last day of the previous month. See below

=MAX(IF(MONTH(A2:A11)=MONTH((DATE(YEAR(Now),MONTH(NOW()),1)-1)),B2:B11))

 

[drose]

Hi,
When I tried this it gave me an error, (the value used in the formula is the
wrong data type) below is the formula I used and G2:G232 either have dates
like 1/2/2009 or they are blank. Any ideas?
Thanks

=MAX(IF(MONTH(G2:G232)=MONTH(TODAY())-1,H2:H232))

 

[FSt1]

hi
i tested now() and today() on test data in 2003 and both worked. a blank
date cell produced a result of zero ie no data returned.
i noticed your first sample formula keyed on columns A and B while the
second formula keys on columns G and H. what changed? how are you dates
formated?

Regards
FSt1