# Formula Question

#### brettvba

##### MrExcel MVP
Ok wondering If there is a faster way of doing this.

I have a list of suppliers with their products and sales information
to work out the days cover I add the total of product on had and divide it by the total of the average weekly sales.

To get this though I have to subtotal all the data then put in the formulas on each line is the suppliername alongside the product description and sales information.

I need to be able to access this information quickly so the current method is a bit slow, and changes the data around.

Any Ideas Vba or Formula orientated?

Brett

Example
Book1
ABCD
2SupplierProductTotalInStockAverageSale/Week
3SupplierProductTotalInStockAverageSale/Week
4SupplierProductTotalInStockAverageSale/Week
5Supplier1ProductTotalInStockAverageSale/Week
6Supplier1ProductTotalInStockAverageSale/Week
7Supplier2ProductTotalInStockAverageSale/Week
8Supplier2ProductTotalInStockAverageSale/Week
Sheet1

This message was edited by brettvba on 2002-09-09 20:15

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

##### MrExcel MVP
brett,

a little clarification please - I take it you are adding up each 'total in stock' for each change in supplier & product (same for the average...). So you'd have something like

=sum(c2:c4) / sum(d2 / d4)

?

also, do you have a master list of suppliers & products?

I'm thinking a sumproduct is the way to go here...

#### brettvba

##### MrExcel MVP
At present I goto Data|Subtotals and add sum , Product On Hand ,AverageWeeklySale

So ineffect the formula i use is
POH/AWS*7 ,(*7) is to get an Average Weekly Sale

I was wondering if this was possible without having to subtotal?

Brett

##### MrExcel MVP
still a bit confused (no change there!). could you provide a worked through example of the data & the calculation & expected result...

#### brettvba

##### MrExcel MVP

Ok this is one supplier worked out, I have about 1200 suppliers
Book1
ABCDE
1QuantityonhandAverageWeeklySaleDaysCover
2SupplierProduct5510
3SupplierProduct4515
4SupplierProduct3520
5Totals1354521
Sheet1

Hope this helps

Brett

EDIT:
Formulas Aren't showing up

c5 =Sum(c2:c4)
d5 =Sum(d2:d5)
e5 =c5/d5*7
This message was edited by brettvba on 2002-09-09 21:11

##### MrExcel MVP
with no thought to optimisation, and with a master list of the appropriate sort:
Book7
ABCD
1SupProdNumAve
2supplierProduct5510
3supplierProduct4515
4supplierProduct3520
5supplier2Product26520
6supplier2Product22525
7supplier2Product33020
8
9
10IfonlychangesinSuppliermatter:
11
12supplier21
13supplier212.92308
14
15IfchangesinSupplier&ProductMatter
16
17supplierProduct21
18supplier2Product214
19supplier2Product310.5
20
21
Sheet1

#### brettvba

##### MrExcel MVP

ok Paddy only changes in Supplier Matter

second formula
=SUMIF(\$A\$1:\$A\$6,A9,\$C\$1:\$C\$6)/SUMIF(\$A\$1:\$A\$6,A9,\$D\$1:\$D\$6)*7

so if I wanted to put that formula down the far right row but only have the formula show up once for each supplier, what would I add to that formula.

Brett

##### MrExcel MVP
assuming colA is sorted by supplier:
Book6
ABCDE
1SupProdNumAve
2supplierproduct551021
3supplierproduct4515-
4supplierproduct3520-
5supplier2product652012.92308
6supplier2product2525-
7supplier2product3020-
8
Sheet1

#### brettvba

##### MrExcel MVP
Great,

Thanks Paddy, I can see that being a definite plus for speed

Brett.

Replies
4
Views
180
Replies
0
Views
98
Replies
36
Views
2K
Replies
8
Views
459
Replies
4
Views
450

1,147,455
Messages
5,741,218
Members
423,649
Latest member
steel1968

### 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.

### Which adblocker are you using?

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

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