Excel Division Help

[Cruiser69]

Hi all.

Could anyone please help with a division query.

The Lots have been split into separate lines from the codes in Column C.
The total cost of the Lots combined are in Column E
Is there a way to divide the total cost by the number of items in the Lot
As there are 7 items within Lot 668, each item would be £17.14
I could divide each Lot but that would take a lot of time.
Is there a Formula or VBA for Column F which could do the job in less time.

[TABLE="width: 508"]
<tbody>[TR]
[TD="width: 63, bgcolor: transparent"]

A​

[/TD]
[TD="width: 212, bgcolor: transparent"]

B​

[/TD]
[TD="width: 123, bgcolor: transparent"]

C​

[/TD]
[TD="width: 83, bgcolor: transparent"]

D​

[/TD]
[TD="width: 95, bgcolor: transparent"]

E​

[/TD]
[TD="width: 103, bgcolor: transparent"]

F​

[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]
LOT
[/TD]
[TD="width: 212, bgcolor: transparent"]DESCRIPTION
[/TD]
[TD="width: 123, bgcolor: transparent"]CODES
[/TD]
[TD="width: 83, bgcolor: transparent"]
VENDOR
[/TD]
[TD="width: 95, bgcolor: transparent"]TOTAL COST
[/TD]
[TD="width: 103, bgcolor: transparent"]INDIVIDUAL COST
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"]QTY OF ASSORTED DECORATIONS
[/TD]
[TD="width: 123, bgcolor: transparent"]4959522
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"]120
[/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4929860
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4944710
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4957066
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4957498
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4831395
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]668
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4911201
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.14
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]669
[/TD]
[TD="width: 212, bgcolor: transparent"]QTY OF ASSORTED FESTIVE LIGHTING
[/TD]
[TD="width: 123, bgcolor: transparent"]4931177
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"]87
[/TD]
[TD="width: 103, bgcolor: transparent"]17.40
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]669
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4948200
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.40
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]669
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4944191
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.40
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]669
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4951716
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.40
[/TD]
[/TR]
[TR]
[TD="width: 63, bgcolor: transparent"]669
[/TD]
[TD="width: 212, bgcolor: transparent"].
[/TD]
[TD="width: 123, bgcolor: transparent"]4957002
[/TD]
[TD="width: 83, bgcolor: transparent"]JL-420
[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]17.40
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks for looking

Graham

 

[AlanY]

copy F2 down

Book1
	A	B	C	D	E	F
1	LOT	DESCRIPTION	CODES	VENDOR	TOTAL COST	INDIVIDUAL COST
2	668	QTY OF ASSORTED DECORATIONS	4959522	JL-420	120	17.14
3	668	.	4929860	JL-420		17.14
4	668	.	4944710	JL-420		17.14
5	668	.	4957066	JL-420		17.14
6	668	.	4957498	JL-420		17.14
7	668	.	4831395	JL-420		17.14
8	668	.	4911201	JL-420		17.14
9	669	QTY OF ASSORTED FESTIVE LIGHTING	4931177	JL-420	87	17.40
10	669	.	4948200	JL-420		17.40
11	669	.	4944191	JL-420		17.40
12	669	.	4951716	JL-420		17.40
13	669	.	4957002	JL-420		17.40
Sheet4

Cell Formulas
Range	Formula
F2	=SUMIF(A:A,A2,E:E)/COUNTIF(A:A,A2)

 

[Finalfight40]

Edit: Removed mine as Alan's is better

 

[Cruiser69]

Thanks a lot.

This is exactly what I was looking for

Regards,

Graham

 

[AlanY]

you're welome