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

[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!

 

[Don Guillett]

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!

Easier if you send me the file and this msg dguillett1 @gmail.com

 

[Ron Rosenfeld]

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.

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.

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)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[Stan Brown]

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.

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)))

Great; thanks!

 

[isabelle]

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

 

[Stan Brown]

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

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?

 

[isabelle]

hi,

it works fine with XL2002,
good work!

--
isabelle




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

 

[Stan Brown]

it works fine with XL2002,
good work!

Thanks to those who replied. Though I know about array formulas, I
didn't think to use one in this case.