Formula for 4 for 3

[BAMSteve]

Hi there. i have been trying to work out if its possible to create an excel formula to work out a 4 for 3 calculation. e.g.

product 1 = £34.99
Product 2 = £25.99
Product 3 = £30.99
Product 4 = £29.99

It will add up the 4 items but remove the cheapest item in this case product 2 and return the figure £95.97

would this be possible?

then to scale this up. e.g. 8 items bought, 2 cheapest would be free.

thank you in advance

Steve

 

[kweaver]

=sum(a1:a4)-min(a1:a4)

 

[Eric W]

Consider:

Book1
	B	C	D	E	F	G	H
1				Values	Sum	# free	Sum
2	34.99	95.97		1	18	3	15
3	25.99			2
4	30.99			3
5	29.99			4
6				5
7				6
Sheet3

Cell Formulas
Range	Formula
C2	=SUM(B2:B5)-MIN(B2:B5)
F2	=SUM(E2:E10)-SUM(SMALL(E2:E10,{1,2}))
H2	{=SUM(E2:E10)-SUM(SMALL(E2:E10,ROW(INDIRECT("1:"&G2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The C2 formula is the sum minus the least expensive. The F2 formula is the sum minus the least 2 expensive. The H2 formula is the sum minus the least n expensive, where n is in G3.

Hope this helps!

 

[BAMSteve]

Thank you both!!!

 

[Eric W]

Glad we could help.