Sum N number of values from a list, highest first.

[SW1044]

Hi,

I couldn't think of a clear way to title this post I'm afraid!

In this example there are several different products for sale and we record the number of each that are sold. Each product has a commission payment and the sales person receives commission for all sales EXCEPT 4 sales of the lowest ranked items.

The items are ranked with the lowest on the left. So to do this manually we start with the leftmost item and count the sales until we reach 4 sales. Then from the 5th sale onwards we multipy the sales by the commission figures and add the results up to arrive at the final commission figure.

I've given this a lot of thought and can't think of any way to achieve it without VBA. Unfortunately my Excel VBA skills are not up to the job!

I've be very grateful for any solutions or pointers thanks in advance.

 

[Andrew Poulsom]

Can you explain how you calculated the commission figures in your sample data please?

 

[SW1044]

Can you explain how you calculated the commission figures in your sample data please?

Hi Andrew, we calculated them on paper (as decribed in para 3) and then we typed the figure in to the cell.

If this isn't clear I'll elaborate.

Cheers, Scott.

 

[jasonb75]

Which version of excel are you using?

I have a way to do this without VBA, which would be easier with excel 07 onwards and a couple of helper columns.

 

[SW1044]

ADVERTISEMENT

Which version of excel are you using?

I have a way to do this without VBA, which would be easier with excel 07 onwards and a couple of helper columns.

Thanks, we have Excel 2010. I've be very intested to see how it's done without VBA.

 

[barry houdini]

I assume that Andrew asked about the commission calculations because the results don't seem to match your description

For row 4 there's a 10 in E4 so presumably you reduce that by 4 (to 6) and then all the other figures count, so assuming commissions in row 1 you get

=(6*1)+(5*5)+(2*5)+(1*10)=51

Your figure is 75, how do you get that?

 

[Andrew Poulsom]

ADVERTISEMENT

I assume that Andrew asked about the commission calculations because the results don't seem to match your description

For row 4 there's a 10 in E4 so presumably you reduce that by 4 (to 6) and then all the other figures count, so assuming commissions in row 1 you get

=(6*1)+(5*5)+(2*5)+(1*10)=51

Your figure is 75, how do you get that?

My sentiments exactly.

 

[vmser]

I assume that Andrew asked about the commission calculations because the results don't seem to match your description

For row 4 there's a 10 in E4 so presumably you reduce that by 4 (to 6) and then all the other figures count, so assuming commissions in row 1 you get

=(6*1)+(5*5)+(2*5)+(1*10)=51

Your figure is 75, how do you get that?

My math says:
4 or less sold is 5.00
For each item sold over 4 pieces, another 5.00 is added.

In that case they did mess up the last line, which should be 50.
(which is also why it'd be wiser for them to hard-code the formula into Excel )

 

[jasonb75]

This appears to have the right results, hopefully Andrew or Barry will notice any errors that I've missed.

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	1	5	5	5	8	8	10	10
2
3										Total Sold	Over 4		Helper	Commission
4	10	0	5	2	0	0	0	1		18	14		2	51.00
5	5	0	8	2	1	0	0	0		16	12		2	59.00
6	0	0	1	0	0	0	0	0		1	0			-
7	3	0	3	2	0	0	0	1		9	5		4	30.00
8	4	0	8	1	0	0	0	0		13	9		4	45.00
Sheet1

 

[barry houdini]

My math says:
4 or less sold is 5.00
For each item sold over 4 pieces, another 5.00 is added.

In that case they did mess up the last line, which should be 50.
(which is also why it'd be wiser for them to hard-code the formula into Excel )

I don't understand that, aren't you using the commission figures in E1:L1?

This formula in P4 should give you the same results as Jason's without any helper columns

=SUM(IF(SUBTOTAL(9,OFFSET(D4,0,0,1,COLUMN(E4:L4)-COLUMN(E4)+1))< SUM(D4,4),TEXT(E4:L4+SUBTOTAL(9,OFFSET(D4,0,0,1,COLUMN(E4:L4)-COLUMN(E4)+1))-SUM(D4,4),"[<0]""0"";0"),E4:L4)*E$1:L$1)

confirmed with CTRL+SHIFT+ENTER and copied down