# 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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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...

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

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

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

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

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

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

Great,

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

Brett.

Replies
0
Views
458
Replies
1
Views
807
Replies
2
Views
232
Replies
1
Views
687
Replies
4
Views
397

1,218,620
Messages
6,143,520
Members
450,492
Latest member
Rusbus1972

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