| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return different values based on 4 criteria?
Need Formula or macro to allocate the "Scheduled hours" based on Group Counts:
If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" should both be 6 hours If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" should both be 0 hours If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" should 12 hours If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" should 12 hours What should be returned for Scheduled Hours Group Count Scheduled Hours A B A B 1047 1192 6 6 0 995 0 12 1752 0 12 0 0 0 0 0 0 0 0 0 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return different values based on 4 criteria?
- Assume Group A is in column A
- Assume Group C is in column B - Assume data begins in row 2 - Scheduled Hours for Group A would be... =IF(AND(A20,B20),6,IF(AND(A2<=0,B2<=0),0,IF(AND( A20,B2=0),12,0))) -Scheduled Hours for Group C would be... =IF(AND(A20,B20),6,IF(AND(A2<=0,B2<=0),0,IF(AND( A2=0,B20),12,0))) -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Susie (SFAngelgirl)" wrote: Need Formula or macro to allocate the "Scheduled hours" based on Group Counts: If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" should both be 6 hours If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" should both be 0 hours If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" should 12 hours If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" should 12 hours What should be returned for Scheduled Hours Group Count Scheduled Hours A B A B 1047 1192 6 6 0 995 0 12 1752 0 12 0 0 0 0 0 0 0 0 0 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return different values based on 4 criteria?
Scheduled Hours for Group A
=MAX((A21)*12-(B21)*6,0) Scheduled Hours for Group C =MAX((B21)*12-(A21)*6,0) "Susie (SFAngelgirl)" wrote: Need Formula or macro to allocate the "Scheduled hours" based on Group Counts: If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" should both be 6 hours If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" should both be 0 hours If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" should 12 hours If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" should 12 hours What should be returned for Scheduled Hours Group Count Scheduled Hours A B A B 1047 1192 6 6 0 995 0 12 1752 0 12 0 0 0 0 0 0 0 0 0 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return different values based on 4 criteria?
Worked on the first try - thank you - It saved me hours of work
-- Thanks - Suze "Teethless mama" wrote: Scheduled Hours for Group A =MAX((A21)*12-(B21)*6,0) Scheduled Hours for Group C =MAX((B21)*12-(A21)*6,0) "Susie (SFAngelgirl)" wrote: Need Formula or macro to allocate the "Scheduled hours" based on Group Counts: If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" should both be 6 hours If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" should both be 0 hours If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" should 12 hours If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" should 12 hours What should be returned for Scheduled Hours Group Count Scheduled Hours A B A B 1047 1192 6 6 0 995 0 12 1752 0 12 0 0 0 0 0 0 0 0 0 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
return different values based on 4 criteria?
Teethless Mama provided a MAX function that worked great! I couldn't get
your answer to work just right - but it helped me with anohter nested IF AND issue. THank you Gary -- Thanks - Suze "Gary Brown" wrote: - Assume Group A is in column A - Assume Group C is in column B - Assume data begins in row 2 - Scheduled Hours for Group A would be... =IF(AND(A20,B20),6,IF(AND(A2<=0,B2<=0),0,IF(AND( A20,B2=0),12,0))) -Scheduled Hours for Group C would be... =IF(AND(A20,B20),6,IF(AND(A2<=0,B2<=0),0,IF(AND( A2=0,B20),12,0))) -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Susie (SFAngelgirl)" wrote: Need Formula or macro to allocate the "Scheduled hours" based on Group Counts: If Group "A" and "C" are both greater than 1, then Scheduled "A" and "C" should both be 6 hours If Group "A" and "C" are equal to or less than 0, then Scheduled "A" and "C" should both be 0 hours If Group "A" greater than 1, and Group "C" is equal to 0, then Scheduled "A" should 12 hours If Group "C" greater than 1, and Group "A" is equal to 0, then Scheduled "C" should 12 hours What should be returned for Scheduled Hours Group Count Scheduled Hours A B A B 1047 1192 6 6 0 995 0 12 1752 0 12 0 0 0 0 0 0 0 0 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return values from an array based on more than one criteria | Excel Discussion (Misc queries) | |||
| Return value based on certain criteria | Excel Worksheet Functions | |||
| Return value based on criteria | Excel Discussion (Misc queries) | |||
| Return a value based on two criteria | Excel Discussion (Misc queries) | |||
| return value based on two criteria | Excel Worksheet Functions | |||
![Susie (SFAngelgirl)[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
