Large function & summing the largest 30 amounts in one f

R

rb

New Member
Joined
Sep 8, 2003
Messages
22
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Re: Large function & summing the largest 30 amounts in o

Is it possible to show a small example of what retail_sales looks like ? and also, is it sorted (and if so, how ?), and if not, could it be ? also, can you use extra columns to place intermediate calculations ?
 
Upvote 0
Re: Large function & summing the largest 30 amounts in o

Example:

Retail Sales Division Week Year
89,216 WOMENS WEEK 1 2003
61,434 WOMENS WEEK 1 2003
58,352 GIRLS WEEK 2 2003
57,208 MEN'S WEEK 3 2003
53,653 MEN'S WEEK 3 2003
52,342 WOMENS WEEK 1 2003
49,704 MEN'S WEEK 1 2003
49,413 WOMENS WEEK 2 2002
48,806 WOMENS WEEK 1 2002
48,042 MEN'S WEEK 4 2002

I could use a macro to sort it by year, week and division. I'm not sure how intermediate calculations might work besides the large formula I used as there are 40,000 row of this type of data. I am using that formula as an intermediary to get the total of each top 30 $ and then pulling that data into a summary sheet....Thanks.
 
Upvote 0
Re: Large function & summing the largest 30 amounts in o

Sorry for these ugly formulas, but I had to give it a try. I'm not sure about this. I could not post big enough picture due to size but formulas doesnt have to go more than 30 rows down (number of options). (Any ; in the formulas should be changed to ,)
Book2.xls
ABCDEFGHIJKLM
1RetailSalesDivisionWeekYearDivisionWeekYearSUMof30largest
2$100,00men1200311368240boy520028760
3$101,00men220035111358241boy420032321
4$102,00girl320035331358240boy420022310
5$103,00boy420035531284912girl520028550
6$104,00baby520038031264913girl320032299
7$105,00men120038251264912girl320022290
8$106,00men220038451262992men520028860
9$107,00girl3200313651232993men220032277
10$108,00boy4200313871232992men220022270
11$109,00baby5200314071222993men120032255
Sheet1
 
Upvote 0
Re: Large function & summing the largest 30 amounts in o

Thank you.

These formulas are definitly more advanced than I have worked with. It will take me awhile to understand them. Although I do aspire to be excellent at Excel someday.
 
Upvote 0
Re: Large function & summing the largest 30 amounts in o

rb said:
...I could use a macro to sort it by year, week and division. I'm not sure how intermediate calculations might work besides the large formula I used as there are 40,000 row of this type of data. I am using that formula as an intermediary to get the total of each top 30 $ and then pulling that data into a summary sheet....
Click to expand...

If you are willing to sort as you specify and add an additional column to concatenate relevant columns as shown in the exhibit:
Book2.xls
ABCDEFGHIJ
1RetailSalesDivisionWeekYearConcat4
258,352GIRLSWEEK22003GIRLS2003WEEK2WOMEN'S2003WEEK1202,992202,992
348,042MEN'SWEEK42002MEN'S2002WEEK4
449,704MEN'SWEEK12003MEN'S2003WEEK1
557,208MEN'SWEEK32003MEN'S2003WEEK3
653,653MEN'SWEEK32003MEN'S2003WEEK3
748,806WOMEN'SWEEK12002WOMEN'S2002WEEK1
849,413WOMEN'SWEEK22002WOMEN'S2002WEEK2
989,216WOMEN'SWEEK12003WOMEN'S2003WEEK1
1061,434WOMEN'SWEEK12003WOMEN'S2003WEEK1
1152,342WOMEN'SWEEK12003WOMEN'S2003WEEK1
Sheet1


Formulas:

E2, which is copied down:

=B2&CHAR(127)&D2&CHAR(127)&C2

You can use one of:

I2:

=SUM(LARGE(V(INDEX(A:A,MATCH(F2&CHAR(127)&G2&CHAR(127)&H2,E:E,0)):INDEX(A:A,MATCH(F2&CHAR(127)&G2&CHAR(127)&H2,E:E))),ROW(INDIRECT("1:"&MIN(COUNT(V()),$I$1)))))

which must be confirmed with control+shift+enter, not just with enter.

This formula looks at just the relevant range. V is a UDF that you need to add to your workbook as a module:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

The formula that follows uses the morefunc.xll add-in in order to avoid INDIRECT and invokes GETV/SETV, equivalent of V. It should be faster than the foregoing.

J2:

=SUM(LARGE(SETV(INDEX(A:A,MATCH(F2&CHAR(127)&G2&CHAR(127)&H2,E:E,0)):INDEX(A:A,MATCH(F2&CHAR(127)&G2&CHAR(127)&H2,E:E))),INTVECTOR(MIN(COUNT(GETV()),$I$1),1)))

which you also need to confirm with control+shift+enter, instead of just enter.

If you try them out, please report back how they behave qua speed.
 
Upvote 0
Re: Large function & summing the largest 30 amounts in o

These are amazing formulas. I'm working on using the first one in "I2", but haven't quite gotten it to work. I've run into the #Name error and am trying to figure out why. Once I get it working...I'll let you know the result.

Thanks

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

rb said:
These are amazing formulas. I'm working on using the first one in "I2", but haven't quite gotten it to work. I've run into the #Name error and am trying to figure out why. Once I get it working...I'll let you know the result.

Thanks

Rick
Click to expand...

If you're trying out the first one with V, you need to copy the code for V to your workbook as a module: Here is the recipe.

Close all Excel files except the target workbook.
Activate Tools|Macro|Visual Basic Editor.
Activate Insert|Module.
Copy the code and paste it in the window entitled "...(Code)".
Activate File|Close and Return to Microsoft Excel.

If you want to try the one with SETV/GETV and INTVECTOR, you need to install morefunc.xll and activate this add-in via Tools|Add-Ins.
 
Upvote 0
Re: Large function & summing the largest 30 amounts in o

There's something wrong with the two worksheets in this thread. I cannot view the different formulas. :cry:
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
161
Cubist
Cubist
S
  • Question Question
Excluding dates in summing up a range
Replies
3
Views
258
Fluff
Fluff
S
  • Solved
SumIf problem
Replies
2
Views
419
sharshra
S
Ramballah
  • Solved
Removing duplicates and adding amounts
2
Replies
18
Views
404
Fluff
Fluff
S
  • Question Question
Scan function in calculated field in excel pivot table
Replies
3
Views
318
RoryA
RoryA

Forum statistics

Threads
1,225,969
Messages
6,188,109
Members
453,460
Latest member
Cjohnson3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top