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.

 

[Aladin Akyurek]

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

Somehow I was able to repeat your success with the data you provided, but
I haven't been able to get this formula to work on my data. I keep getting various errors such as #Value, #N/A, etc. I have spent a few hours trying. Hopefully I can figure it out. It seems like a great idea.

I hope the data itself does not have any error values like #VALUE! or #N/A.

I trust you did the sorting then concating.

 

[rb]

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

I couldn't find any errors. I normally us "if(iserror...." around formulas and then hardcode the data to ensure there are no problems. I must be doing something wrong, but can't figure out what. Since your Macro is Public, it should certainly work on all worksheets...so that can't be an issue. I also often use the Trim function to ensure there are no extra spaces.

I know if I could get it to work, it would be great. I appreciate all the time you are spending on this.

Rick

 

[rb]

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

Forgot....yes, I did sort the data first and then Concatenate.

 

[Aladin Akyurek]

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

Forgot....yes, I did sort the data first and then Concatenate.

You still need to confirm the end formula with control+shift+enter, not just with enter. Forgot that maybe?

 

[rb]

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

Yes...I did that as well. I've used Sumif arrays and such before so am familiar. I'll keep trying to figure it out....thanks.

 

[rb]

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

Oh, I am also using the Insert --> Names method to ensure all my ranges and location of data are correct.

 

[Ekim]

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

Rick,

I appreciate that you are looking for a formula but the following procedure works. It may appear to be long-winded but the procedure is very simple to execute and uses Excel’s built-in functions – Sort, Sub-totals, Text to Columns etc.

The procedure assumes the data layout shown by you i.e.:
RetailSales Division Week Year

Step 1
Sort your data by year, week and division.

Step 2
In E1 put the label “Concat” (no quotes)
In F2:
=B2&" "&D2&" "&C2
copy down

Step 3
Select all the data
Go to Data | Subtotals
At each change in: Concat
Use function: Sum
Add subtotals to: Retail Sales
OK
Click the level 2 button to close the sub-totals up

Step 4
Go to the Data menu | Sort
Select the data (include the header row but exclude the last row that has “Grand Total”)
Sort by Retail Sales (descending)
OK

Step 5
Highlight your top 30.
Go to the Edit menu and select “Go To” (or hit function key 5)
Click the “Special” button’ then click the radio button for “Visible cells only”
OK

Step 6
With the visible cells highlighted:
Right click your mouse
Select Copy
Switch to a blank sheet
Right click your mouse
Select Paste

Step 7
Select column E (i.e. the column with the concatenation)
Go to the Data menu | Text to Columns
Select Delimiter then OK
Click the delimiter box for Space
Click Finish


HTH

Mike

 

[rb]

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

Thanks....I'll give it a shot today. Unfortunately, Excel just isn't normally meant for 40K plus rows of data. But I sure am learning alot trying. Ultimatley I'm trying to automate the whole thing by recording some macros.

 

[rb]

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

Ekim,

I looked at you method, but it seems it would only work with one set of Top 30.

I have about 6 sets of Top 30 totals I need for each week, so not sure how this would work. I have 40K rows of data with Six divisions with up to 5 weeks each per year.

It's starting to seem like what I am trying to do is beyond Excel's capability.

Thanks

 

[rb]

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

I wonder if there is a VBA solution. If I sort the data and then look in the "division" column. If each time the name changes it goes to the Sales Amount column and then sums the first downward 30 items and puts this information in a designated cell. Then repeats the process for the next new Item etc. I am quite green at VBA, so not sure if this is possible.

It seems like no formulas will work on this much data.