Large function & summing the largest 30 amounts in one f

[rb]

Is there a formula where I can use the "Large" function and get a sum of the 30 largest items all in one formula?

Someone was kind enough to provide me with this array formula: =LARGE(IF(Division=$B$5,IF(Week=$H$4,IF(Year=TY,Retail_Sales))),4). This formula works great....but: Currently it is taking 1 1/2 hrs to calculate my formulas. I have this formula in 7,200 cells. I have about 40,000 rows of raw data with 15 columns they are looking at. I can't use a pivot table because it blows up (too many unique items that exceed its capacity)

Currently I have to use the formula 30 times to get all of the Top 30 amounts and then sum them. I have to do this for each of 6 divisions for each of 5 weeks and 2 years which equals 1,800 cells. I then doing this for 4 different amount columns which brings it up to 7,200 cells.

Divisions Weeks Years Amount
Mens 1 2002 $$$
Womens 2 2003
Accessories 3
Boys 4
Girls 5
Baby

As you can see from the formula, I am pulling in dollars if they meet the conditions of "Division", "Week", & "Year". There are six different divisions.

Is there any good solution? Thank you for any help you can provide.

 

[rb]

Re: Large function & summing the largest 30 amounts in o

Aladin,

It seems like the formula you provided works well until there is too much data then it starts getting the #Value error.

I sorted by year week division and the formula worked on the first division "Access" "Week 1" then on "Accesse's" "Week 2", but then broke down at about line 9,000 where "Access" "Week 3" is. When I tried to get "Baby" "Week 1" and the other divisions, it didn't work. Maybe just too much data?

Access
Baby
Boys
Girls
Men's
Womens

I'm definitly not having much luck with this problem....But I feel very lucky and appreciate everyone that is trying to help.

 

[Aladin Akyurek]

Re: Large function & summing the largest 30 amounts in o

Aladin,

It seems like the formula you provided works well until there is too much data then it starts getting the #Value error.

I sorted by year week division and the formula worked on the first division "Access" "Week 1" then on "Accesse's" "Week 2", but then broke down at about line 9,000 where "Access" "Week 3" is. When I tried to get "Baby" "Week 1" and the other divisions, it didn't work. Maybe just too much data?

Access
Baby
Boys
Girls
Men's
Womens

I'm definitly not having much luck with this problem....But I feel very lucky and appreciate everyone that is trying to help.

The sort order the exhibit I posted uses is: Division, Year, Weak. The formula depends on that order.

 

[fairwinds]

Re: Large function & summing the largest 30 amounts in o

Just curious...
Did you ever try my suggestion at the beginning of this thread?
I just tested it for 20 000 rows without any sorting ,and it actually worked exept for one error: If the 30:eth value for a group appears more than once it gives you a faulty result. But I think that could be fixed...

 

[rb]

Re: Large function & summing the largest 30 amounts in o

Aladin,

Sorry....Yes, I did you Division, Year, Week with your formula.

Thanks

 

[rb]

Re: Large function & summing the largest 30 amounts in o

Fairwinds,

Thanks....yes, when I tried your formula at about 40K lines it was taking a very long time to calculate.

Rick

 

[rb]

Re: Large function & summing the largest 30 amounts in o

Fairwinds,

Actually, maybe I'm not understanding how to apply your formula correctly. Is this with or without using a filter on all the data. Also, what dose CODE do?

Thanks

 

[fairwinds]

Re: Large function & summing the largest 30 amounts in o

Yes you are right, mine takes too long time, go with Aladin!
(Code changed the letters into numbers creating a unique number for each group in order to rank them)