| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Determine a result of one column based on conditions in two column | Excel Discussion (Misc queries) | |||
| COUNT based on conditions in 2 or more columns | Excel Worksheet Functions | |||
| Adding up entries in one column with conditions in two columns | Excel Discussion (Misc queries) | |||
| Formula to Extract value on 3 column based on two conditions | Excel Discussion (Misc queries) | |||
| count number of cells based on TWO conditions (2 different columns | Excel Worksheet Functions | |||
Linear Mode
