Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF/OR/AND Statement Help
I have a schedule with each project listed and all resources assigned to each
project by area of expertise (AOE). The top of the schedule represents the weeks of the year by dates (rows 7 and 8). I assign a start date and end date next to each resource in columns G and H, depending on where the date falls in rows 7 and 8 it gives me a 1 in that corresponding cell next to that resource. It also populates a 1 in each cell thereafter until it reaches the end date assigned. Column F = Resource Name Column G = Start Dates Column H = End Dates Row 7 = Date of end of week (Sunday) Row 8 = Date of beginning of week (Monday) My formula started like this and it works fine, I want this to work for all my resources I assign. IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$G3 6,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1,"" )) Now for the additional condition. If I am short a resource to assign, I provide it a generic name in column F like MX-XXX, STA-XXX, etc. Based off AOE. I want a formula that can recognize these generic names that I have predefined and provide a -1 in place of the 1 due to the shortage. I attempted this addition to the formula which did not work, but I just may have the syntax incorrect. IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,"")),(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8 =$G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)) ,1,"")) Please help. Here is an example. 5/27/07 6/3/07 ...... 7/15/07 5/21/07 5/28/07 ...... 7/9/07 D F G H PROJECT D.Baker 06/02/07 12/28/07 1 ............................. MX-XXX 06/03/07 7/15/07 -1 ................. -1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF/OR/AND Statement Help
...guessing a bit here .....that there is a missing IF ...
=IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$ G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1, "")) "Gayla" wrote: I have a schedule with each project listed and all resources assigned to each project by area of expertise (AOE). The top of the schedule represents the weeks of the year by dates (rows 7 and 8). I assign a start date and end date next to each resource in columns G and H, depending on where the date falls in rows 7 and 8 it gives me a 1 in that corresponding cell next to that resource. It also populates a 1 in each cell thereafter until it reaches the end date assigned. Column F = Resource Name Column G = Start Dates Column H = End Dates Row 7 = Date of end of week (Sunday) Row 8 = Date of beginning of week (Monday) My formula started like this and it works fine, I want this to work for all my resources I assign. IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$G3 6,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1,"" )) Now for the additional condition. If I am short a resource to assign, I provide it a generic name in column F like MX-XXX, STA-XXX, etc. Based off AOE. I want a formula that can recognize these generic names that I have predefined and provide a -1 in place of the 1 due to the shortage. I attempted this addition to the formula which did not work, but I just may have the syntax incorrect. IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,"")),(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8 =$G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)) ,1,"")) Please help. Here is an example. 5/27/07 6/3/07 ...... 7/15/07 5/21/07 5/28/07 ...... 7/9/07 D F G H PROJECT D.Baker 06/02/07 12/28/07 1 ............................ MX-XXX 06/03/07 7/15/07 -1 ................ -1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF/OR/AND Statement Help
Thanks for the help - but still no change.
"Toppers" wrote: ..guessing a bit here .....that there is a missing IF ... =IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$ G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1, "")) "Gayla" wrote: I have a schedule with each project listed and all resources assigned to each project by area of expertise (AOE). The top of the schedule represents the weeks of the year by dates (rows 7 and 8). I assign a start date and end date next to each resource in columns G and H, depending on where the date falls in rows 7 and 8 it gives me a 1 in that corresponding cell next to that resource. It also populates a 1 in each cell thereafter until it reaches the end date assigned. Column F = Resource Name Column G = Start Dates Column H = End Dates Row 7 = Date of end of week (Sunday) Row 8 = Date of beginning of week (Monday) My formula started like this and it works fine, I want this to work for all my resources I assign. IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$G3 6,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1,"" )) Now for the additional condition. If I am short a resource to assign, I provide it a generic name in column F like MX-XXX, STA-XXX, etc. Based off AOE. I want a formula that can recognize these generic names that I have predefined and provide a -1 in place of the 1 due to the shortage. I attempted this addition to the formula which did not work, but I just may have the syntax incorrect. IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,"")),(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8 =$G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)) ,1,"")) Please help. Here is an example. 5/27/07 6/3/07 ...... 7/15/07 5/21/07 5/28/07 ...... 7/9/07 D F G H PROJECT D.Baker 06/02/07 12/28/07 1 ............................ MX-XXX 06/03/07 7/15/07 -1 ................ -1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested IF/OR/AND Statement Help
Can you send a sample w/book?
toppers <at johntopley.fsnet.co.uk "Gayla" wrote: Thanks for the help - but still no change. "Toppers" wrote: ..guessing a bit here .....that there is a missing IF ... =IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$ G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1, "")) "Gayla" wrote: I have a schedule with each project listed and all resources assigned to each project by area of expertise (AOE). The top of the schedule represents the weeks of the year by dates (rows 7 and 8). I assign a start date and end date next to each resource in columns G and H, depending on where the date falls in rows 7 and 8 it gives me a 1 in that corresponding cell next to that resource. It also populates a 1 in each cell thereafter until it reaches the end date assigned. Column F = Resource Name Column G = Start Dates Column H = End Dates Row 7 = Date of end of week (Sunday) Row 8 = Date of beginning of week (Monday) My formula started like this and it works fine, I want this to work for all my resources I assign. IF(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8=$G3 6,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)),1,"" )) Now for the additional condition. If I am short a resource to assign, I provide it a generic name in column F like MX-XXX, STA-XXX, etc. Based off AOE. I want a formula that can recognize these generic names that I have predefined and provide a -1 in place of the 1 due to the shortage. I attempted this addition to the formula which did not work, but I just may have the syntax incorrect. IF(OR(AND($G36=Z$8,$G36<=Z$7, $F36="MX-XXX"),AND($I$8:$BH$8=$G36,$I$7:$BH$7<=$H36, $F36="MX-XXX"),AND($H36=Z$8,$H36<=Z$7,$F36="MX-XXX")),-1,"")),(OR(AND($G36=Z$8,$G36<=Z$7),AND($I$8:$BH$8 =$G36,$I$7:$BH$7<=$H36),AND($H36=Z$8,$H36<=Z$7)) ,1,"")) Please help. Here is an example. 5/27/07 6/3/07 ...... 7/15/07 5/21/07 5/28/07 ...... 7/9/07 D F G H PROJECT D.Baker 06/02/07 12/28/07 1 ............................ MX-XXX 06/03/07 7/15/07 -1 ................ -1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF STATEMENT - NESTED | Excel Worksheet Functions | |||
Nested IF statement | Excel Worksheet Functions | |||
Nested If Statement | Excel Discussion (Misc queries) | |||
Nested if then else statement | Excel Worksheet Functions | |||
Nested If statement | Excel Worksheet Functions |