Archive of Mr Excel Message Board
Back to Dates in Excel archive index
Back to archive home
Array formula question
Posted by Andonny on June 18, 2001 2:59 AM
Hi,
I am using the array formula below but it is causing me a bit of trouble because it takes about 45 minutes to calculate one sheet. Is there an alternative formula I could use to speed up my calculating process.
=SUM((Sheet1!$B$1:$B$20000=$A5)*(Sheet1!$E$1:$E$20000=D$1)*(Sheet1!$D$1:$D$20000))
Thank you very much for your help
Andonny
Re: Array formula question
Posted by Aladin Akyurek on June 18, 2001 4:16 AM
Andonny,
Take a 4 free columns where you do the calcs:
In F1 enter: =(Sheet1!B1=$A$5)+0
In G1 enter: =(Sheet1!E1=$D$1)+0
In H1 enter: =F1*G1*Sheet1!D1
Select F1:H1 and copy down up to the row 20000.
In I1 enter: =SUM(H:H)
Please time the calc: very curious about the result.
Cheers,
Aladin
=====================
Make that a single col computation
Posted by Aladin Akyurek on June 18, 2001 6:03 AM
:Andonny,
What I suggested can be done in a single column:
In F1 enter: =((Sheet1!B1=$A$5)+0)*((Sheet1!E1=$D$1)+0)*(Sheet1!D1)
Copy down this up to the row 20000.
Then of course in G1:
=SUM(F:F)
This must be cheaper than 3-column version.
Aladin
=====================
Re: Sorry some difficulties
Posted by Andonny on June 18, 2001 6:05 AM
Hi,
I am having a bit of difficulties with the recommentation. I am not sure if my formula is clear enough. It is in sheet2 in column D.
Thanks
Andonny
Re: Sorry some difficulties
Posted by Aladin Akyurek on June 18, 2001 6:20 AM
Hi,
Use a unused column on Sheet2. I'd suggest implementing the single column version I suggested below in the thread. That is:
=((Sheet1!B1=$A$5)+0)*((Sheet1!E1=$D$1)+0)*(Sheet1!D1)
Copy down as far as needed (total 20000 rows). Then do a sum on the resulting range.
I'm terribly curious about the results qua time spent doing calcs.
Aladin
================
Re: May I send the sheet
Posted by Andonny on June 19, 2001 2:52 AM
Hi Aladin,
Would you be able to look at my sheet.
Thank you for your kind help
Andonny
Re: May I send the sheet
Posted by Aladin Akyurek on June 19, 2001 8:18 AM
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our
online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.