How to Shorten a repetitive formula to include more references.
 

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references.
 

=SUMPRODUCT(--('[CT 00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12)* '[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39)/ SUMPRODUCT(--('[CT
00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12))

Should get it.

"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references
 

Looks GREAT Sean, but I`m geetting an error message which is highlighting the
second occurance of "Female" (it mentions / highlights it as "array 1") !!??
Any ideas how to overcome this?
Thanks
RR1

"Sean Timmons" wrote:

=SUMPRODUCT(--('[CT 00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12)* '[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39)/ SUMPRODUCT(--('[CT
00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12))

Should get it.

"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references
 

May need to check my stuff. Make sure no space between the / and the second
sumproduct. Also, make sure the "female" has normal quotes on either side...

"Romileyrunner1" wrote:

Looks GREAT Sean, but I`m geetting an error message which is highlighting the
second occurance of "Female" (it mentions / highlights it as "array 1") !!??
Any ideas how to overcome this?
Thanks
RR1

"Sean Timmons" wrote:

=SUMPRODUCT(--('[CT 00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12)* '[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39)/ SUMPRODUCT(--('[CT
00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12))

Should get it.

"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references
 

Yep, saw the space at the second sumproduct and got rid of it and quote marks
are
also OK.
Do I need to menion `[CT06-13.xls]in the second `SUMPRODUCT` to get the full
range of workboks included or not?
Thanks.
RR1


"Sean Timmons" wrote:

May need to check my stuff. Make sure no space between the / and the second
sumproduct. Also, make sure the "female" has normal quotes on either side...

"Romileyrunner1" wrote:

Looks GREAT Sean, but I`m geetting an error message which is highlighting the
second occurance of "Female" (it mentions / highlights it as "array 1") !!??
Any ideas how to overcome this?
Thanks
RR1

"Sean Timmons" wrote:

=SUMPRODUCT(--('[CT 00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12)* '[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39)/ SUMPRODUCT(--('[CT
00-07.xls]Writing'!$E$10:$E$89="Female€�)*--('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12))

Should get it.

"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references.
 

Hi,

You might consider defining range names in the current workbook that refer
to each of the ranges in the other workbooks, for example:
'[CT 00-07.xls]Writing'!$E$10:$E$89 might be E_7

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references
 

Sounds promising Shane, but how do I go about defining a range name?
Thanks
RR1

"Shane Devenshire" wrote:

Hi,

You might consider defining range names in the current workbook that refer
to each of the ranges in the other workbooks, for example:
'[CT 00-07.xls]Writing'!$E$10:$E$89 might be E_7

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1

How to Shorten a repetitive formula to include more references
 

Just had a go at defining range names. ACE. Works a treat .
Thanks Shane.
Woo Hooo!
RR1

"Romileyrunner1" wrote:

Sounds promising Shane, but how do I go about defining a range name?
Thanks
RR1

"Shane Devenshire" wrote:

Hi,

You might consider defining range names in the current workbook that refer
to each of the ranges in the other workbooks, for example:
'[CT 00-07.xls]Writing'!$E$10:$E$89 might be E_7

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Romileyrunner1" wrote:

Hi guys,
Anyone got any ideas of how I can write the following in a shorter way so
that I can include more refences I.E. more of:
IF(('[CT 00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT 00-07.xls]Writing'!$BB$10:$BB$89),

Any more than shown below will be beyond the formula length limit. OR is
there a way of extending the formula length limit!!!???

FORMULA AS IT IS AT PRESENT:

=VLOOKUP(ROUND(AVERAGE(IF(('[CT
00-07.xls]Writing'!$E$10:$E$89="Female")*('[CT
00-07.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
00-07.xls]Writing'!$BB$10:$BB$89),IF(('[CT
01-08.xls]Writing'!$E$10:$E$89="Female")*('[CT
01-08.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
01-08.xls]Writing'!$BB$10:$BB$89),IF(('[CT
02-09.xls]Writing'!$E$10:$E$89="Female")*('[CT
02-09.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
02-09.xls]Writing'!$BB$10:$BB$89),IF(('[CT
03-10.xls]Writing'!$E$10:$E$89="Female")*('[CT
03-10.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
03-10.xls]Writing'!$BB$10:$BB$89),IF(('[CT
04-11.xls]Writing'!$E$10:$E$89="Female")*('[CT
04-11.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
04-11.xls]Writing'!$BB$10:$BB$89),IF(('[CT
05-12.xls]Writing'!$E$10:$E$89="Female")*('[CT
05-12.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
05-12.xls]Writing'!$BB$10:$BB$89),IF(('[CT
06-13.xls]Writing'!$E$10:$E$89="Female")*('[CT
06-13.xls]Writing'!$AC$10:$AC$89=BK12),'[CT
06-13.xls]Writing'!$BB$10:$BB$89)),0),APS!$B$3:$C$39,2,FALSE )

Thanks
RR1