MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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

=====================

Posted by Aladin Akyurek on June 18, 2001 6:03 AM

Make that a single col computation

: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

=====================

Posted by Andonny on June 18, 2001 6:05 AM

Re: Sorry some difficulties

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

Posted by Aladin Akyurek on June 18, 2001 6:20 AM

Re: Sorry some difficulties

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

================

Posted by Andonny on June 19, 2001 2:52 AM

Re: May I send the sheet

Hi Aladin,
Would you be able to look at my sheet.

Thank you for your kind help
Andonny

Posted by Aladin Akyurek on June 19, 2001 8:18 AM

Re: May I send the sheet

Why not? Send in.