Largest value in column A based on conditions in columns B and C

  • Thread starter Thread starter Stan Brown
  • Start date Start date
S

Stan Brown

For some reason I just can't think of the right function to use here.

My biweekly time sheets have dates in (let's say) A11 through A15 and
A21 through A25. The adjacent cells in column B have a 1 for a
vacation day or blank for no vacation day; column C has 1 for company
holiday or blank for no company holiday.

The date submitted is in (let's say) D31. I need a formula that
selects the latest date in (A11:A15,A21:A25) but disregarding any
rows where column B or C has a 1.

Thanks!
 
For some reason I just can't think of the right function to use here.

My biweekly time sheets have dates in (let's say) A11 through A15 and
A21 through A25.  The adjacent cells in column B have a 1 for a
vacation day or blank for no vacation day; column C has 1 for company
holiday or blank for no company holiday.

The date submitted is in (let's say) D31.  I need a formula that
selects the latest date in (A11:A15,A21:A25) but disregarding any
rows where column B or C has a 1.

Thanks!
Click to expand...

Easier if you send me the file and this msg dguillett1 @gmail.com
 
You don't give any indication of the significance of the date in
D31; nor can I guess at that from what you have written.
Click to expand...

Sorry, I didn't realize it mattered. But in case it wasn't obvious
from "time sheet", the dates are M-F within two work weeks.
The latest date in (a11:a15,a21:a25) is given by the formula:
This formula must be **array-entered**:

=MAX(MAX(A11:A15*(B11:B15<>1)*(C11:C15<>1)),MAX(A21:A25*(B21:B25<>1)*(C21:C25<>1)))
Click to expand...

Great; thanks!
 
hi,

even though, but a little shorter

=MAX(A11:A15*(B11:B15<>1)*(C11:C15<>1),MAX(A21:A25*(B21:B25<>1)*(C21:C25<>1)))

formula array, validate with ctrl+shift+enter
 
even though, but a little shorter

=MAX(A11:A15*(B11:B15<>1)*(C11:C15<>1),MAX(A21:A25*(B21:B25<>1)*(C21:C25<>1)))

formula array, validate with ctrl+shift+enter
Click to expand...

Thanks for posting. Following your hint, I actually tried the even
shorter form

=MAX(A11:A15*(B11:B15<>1)*(C11:C15<>1),
A21:A25*(B21:B25<>1)*(C21:C25<>1))

as an array formula, and it worked just fine in Excel 2010. Do you
need the second MAX in an earlier version of Excel?
 
hi,

it works fine with XL2002,
good work!

--
isabelle




Le 2011-11-13 09:05, Stan Brown a écrit :
 
Back
Top