How to Shorten a repetitive formula to include more references.

R

Romileyrunner1

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
 
S

Sean Timmons

=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.
 
R

Romileyrunner1

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 said:
=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 said:
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
Click to expand...
Click to expand...
 
S

Sean Timmons

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 said:
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 said:
=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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Romileyrunner1

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 said:
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 said:
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 said:
=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.

:

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
Click to expand...
Click to expand...
Click to expand...
 
S

Shane Devenshire

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
 
R

Romileyrunner1

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

Shane Devenshire said:
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 said:
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
Click to expand...
Click to expand...
 
R

Romileyrunner1

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

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

Shane Devenshire said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count entries in the column translating to minutes. 5
looking for a way to repeat a formula pattern within a cell 3
Modifying a complex date formual to count months 4
IF ARRAY LOOKUP ??? Help Please 6
Need (Excel) formula to make Mark sheet with Automatic Grading 15
I want formula for multiple criteria using ih differnt column 2
time_report_date-wise_employee-wise 6
Calculating earliest start and latest end dates by contract 4

Top