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

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!


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

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

On Nov 12, 10:22*am, Stan Brown wrote:
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!

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
* * * * * * * * * * * * * * * * * *http://OakRoadSystems.com
Shikata ga nai...

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

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

On Sat, 12 Nov 2011 11:22:25 -0500, Stan Brown wrote:

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(A2 1: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.

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

On Sat, 12 Nov 2011 13:50:25 -0500, Ron Rosenfeld wrote:

On Sat, 12 Nov 2011 11:22:25 -0500, Stan Brown wrote:

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.

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(A2 1:A25*(B21:B25<1)*(C21:C25<1)))

Great; thanks!

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

Largest value in column A based on conditions in columns B andC
 

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

--
isabelle

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

On Sat, 12 Nov 2011 21:39:27 -0500, isabelle wrote:

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?

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

Largest value in column A based on conditions in columns B andC
 

hi,

it works fine with XL2002,
good work!

--
isabelle




Le 2011-11-13 09:05, Stan Brown a écrit :
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?

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

On Sun, 13 Nov 2011 09:43:39 -0500, isabelle wrote:

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...