How do I add more ranges to the following formula?
 

(for Excel 2003)
I've a Yearly calendar and a table with around 27 diferent date ranges for 2
teams (alternating). I want the days in the calendar to change color
according to who is working ex. Red for Team1 and Green to Team2.

In each day of the calendar I used the folowing formula in Condition 1 and
the same with inverse False,True result in Condition 2:
=IF(OR(AND(S6$AA$5,S6<$AB$5),AND(S6$AA$7,S6<$AB$ 7),AND(S6$AA$9,S6<$AB$9),AND(S6$AA$11,S6<$AB$11) ,AND(S6$AA$13,S6<$AB$13),AND(S6$AA$15,S6<$AB$15) ,AND(S6$AA$17,S6<$AB$17),AND(S6$AA$19,S6<$AB$19) ),TRUE,FALSE)

This works for up to 8 ranges. How do I do the same with some other formula
that can accept more ranges?
--
Mario

How do I add more ranges to the following formula?
 

Try this...

=SUMPRODUCT(--(MOD(ROW(AA5:AA19)-ROW(AA5),2)=0),--(S6AA5:AA19),--(S6<AB5:AB19))

--
Biff
Microsoft Excel MVP


"neivamj" wrote in message
...
(for Excel 2003)
I've a Yearly calendar and a table with around 27 diferent date ranges for
2
teams (alternating). I want the days in the calendar to change color
according to who is working ex. Red for Team1 and Green to Team2.

In each day of the calendar I used the folowing formula in Condition 1 and
the same with inverse False,True result in Condition 2:
=IF(OR(AND(S6$AA$5,S6<$AB$5),AND(S6$AA$7,S6<$AB$ 7),AND(S6$AA$9,S6<$AB$9),AND(S6$AA$11,S6<$AB$11) ,AND(S6$AA$13,S6<$AB$13),AND(S6$AA$15,S6<$AB$15) ,AND(S6$AA$17,S6<$AB$17),AND(S6$AA$19,S6<$AB$19) ),TRUE,FALSE)

This works for up to 8 ranges. How do I do the same with some other
formula
that can accept more ranges?
--
Mario

How do I add more ranges to the following formula?
 

Thanks, it workwed perfectly.

--
Mario


"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(MOD(ROW(AA5:AA19)-ROW(AA5),2)=0),--(S6AA5:AA19),--(S6<AB5:AB19))

--
Biff
Microsoft Excel MVP


"neivamj" wrote in message
...
(for Excel 2003)
I've a Yearly calendar and a table with around 27 diferent date ranges for
2
teams (alternating). I want the days in the calendar to change color
according to who is working ex. Red for Team1 and Green to Team2.

In each day of the calendar I used the folowing formula in Condition 1 and
the same with inverse False,True result in Condition 2:
=IF(OR(AND(S6$AA$5,S6<$AB$5),AND(S6$AA$7,S6<$AB$ 7),AND(S6$AA$9,S6<$AB$9),AND(S6$AA$11,S6<$AB$11) ,AND(S6$AA$13,S6<$AB$13),AND(S6$AA$15,S6<$AB$15) ,AND(S6$AA$17,S6<$AB$17),AND(S6$AA$19,S6<$AB$19) ),TRUE,FALSE)

This works for up to 8 ranges. How do I do the same with some other
formula
that can accept more ranges?
--
Mario


.

How do I add more ranges to the following formula?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"neivamj" wrote in message
...
Thanks, it workwed perfectly.

--
Mario


"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(MOD(ROW(AA5:AA19)-ROW(AA5),2)=0),--(S6AA5:AA19),--(S6<AB5:AB19))

--
Biff
Microsoft Excel MVP


"neivamj" wrote in message
...
(for Excel 2003)
I've a Yearly calendar and a table with around 27 diferent date ranges
for
2
teams (alternating). I want the days in the calendar to change color
according to who is working ex. Red for Team1 and Green to Team2.

In each day of the calendar I used the folowing formula in Condition 1
and
the same with inverse False,True result in Condition 2:
=IF(OR(AND(S6$AA$5,S6<$AB$5),AND(S6$AA$7,S6<$AB$ 7),AND(S6$AA$9,S6<$AB$9),AND(S6$AA$11,S6<$AB$11) ,AND(S6$AA$13,S6<$AB$13),AND(S6$AA$15,S6<$AB$15) ,AND(S6$AA$17,S6<$AB$17),AND(S6$AA$19,S6<$AB$19) ),TRUE,FALSE)

This works for up to 8 ranges. How do I do the same with some other
formula
that can accept more ranges?
--
Mario


.