Excel Division Help

Cruiser69

New Member
Joined
Mar 12, 2018
Messages
29
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.


A

B

C

D

E

F

LOT

DESCRIPTION
CODES

VENDOR

TOTAL COST
INDIVIDUAL COST
668
QTY OF ASSORTED DECORATIONS
4959522
JL-420
120
17.14
668
.
4929860
JL-420
17.14
668
.
4944710
JL-420
17.14
668
.
4957066
JL-420
17.14
668
.
4957498
JL-420
17.14
668
.
4831395
JL-420
17.14
668
.
4911201
JL-420
17.14
669
QTY OF ASSORTED FESTIVE LIGHTING
4931177
JL-420
87
17.40
669
.
4948200
JL-420
17.40
669
.
4944191
JL-420
17.40
669
.
4951716
JL-420
17.40
669
.
4957002
JL-420
17.40

<tbody>
</tbody>


Thanks for looking

Graham
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
copy F2 down

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">LOT</td><td style=";">DESCRIPTION</td><td style=";">CODES</td><td style=";">VENDOR</td><td style=";">TOTAL COST</td><td style=";">INDIVIDUAL COST</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">668</td><td style=";">QTY OF ASSORTED DECORATIONS</td><td style="text-align: right;;">4959522</td><td style=";">JL-420</td><td style="text-align: right;;">120</td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">668</td><td style=";">.</td><td style="text-align: right;;">4929860</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">668</td><td style=";">.</td><td style="text-align: right;;">4944710</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">668</td><td style=";">.</td><td style="text-align: right;;">4957066</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">668</td><td style=";">.</td><td style="text-align: right;;">4957498</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">668</td><td style=";">.</td><td style="text-align: right;;">4831395</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">668</td><td style=";">.</td><td style="text-align: right;;">4911201</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">669</td><td style=";">QTY OF ASSORTED FESTIVE LIGHTING</td><td style="text-align: right;;">4931177</td><td style=";">JL-420</td><td style="text-align: right;;">87</td><td style="text-align: right;background-color: #E2EFDA;;">17.40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">669</td><td style=";">.</td><td style="text-align: right;;">4948200</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">669</td><td style=";">.</td><td style="text-align: right;;">4944191</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">669</td><td style=";">.</td><td style="text-align: right;;">4951716</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">669</td><td style=";">.</td><td style="text-align: right;;">4957002</td><td style=";">JL-420</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #E2EFDA;;">17.40</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=SUMIF(<font color="Blue">A:A,A2,E:E</font>)/COUNTIF(<font color="Blue">A:A,A2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
Edit: Removed mine as Alan's is better
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,169
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,172
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top