Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default 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...
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,522
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default 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...
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default 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...
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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?

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 524
Default 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...
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Determine a result of one column based on conditions in two column tel703 Excel Discussion (Misc queries) 1 March 25th 10 06:01 PM
COUNT based on conditions in 2 or more columns Annie1904 Excel Worksheet Functions 3 November 28th 07 05:28 PM
Adding up entries in one column with conditions in two columns The Fool on the Hill Excel Discussion (Misc queries) 5 February 5th 07 04:37 PM
Formula to Extract value on 3 column based on two conditions wayliff Excel Discussion (Misc queries) 0 January 12th 06 09:20 PM
count number of cells based on TWO conditions (2 different columns Troi-Xanh Excel Worksheet Functions 2 February 12th 05 01:46 AM


All times are GMT +1. The time now is 10:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"