Prev: Sharing workbook
Next: Copying values/formulas from only visable lines when filters are o
From: Susie (SFAngelgirl) on 16 Mar 2010 12:53
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

From: Gary Brown on 16 Mar 2010 14:09
- 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(A2>0,B2>0),6,IF(AND(A2<=0,B2<=0),0,IF(AND(A2>0,B2=0),12,0)))

-Scheduled Hours for Group C would be...
=IF(AND(A2>0,B2>0),6,IF(AND(A2<=0,B2<=0),0,IF(AND(A2=0,B2>0),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
>
From: Teethless mama on 16 Mar 2010 15:45
Scheduled Hours for Group A
=MAX((A2>1)*12-(B2>1)*6,0)


Scheduled Hours for Group C
=MAX((B2>1)*12-(A2>1)*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
>
From: Susan in Sacramento on 16 Mar 2010 17:31
Worked on the first try - thank you - It saved me hours of work
--
Thanks - Suze


"Teethless mama" wrote:

> Scheduled Hours for Group A
> =MAX((A2>1)*12-(B2>1)*6,0)
>
>
> Scheduled Hours for Group C
> =MAX((B2>1)*12-(A2>1)*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
> >
 | 
Pages: 1
Prev: Sharing workbook
Next: Copying values/formulas from only visable lines when filters are o