Formula Question

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
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.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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...

Paddy
 

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
still a bit confused (no change there!). could you provide a worked through example of the data & the calculation & expected result...

Paddy
 

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030

ADVERTISEMENT

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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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


Paddy
 

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030

ADVERTISEMENT

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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
assuming colA is sorted by supplier:
Book6
ABCDE
1SupProdNumAve
2supplierproduct551021
3supplierproduct4515-
4supplierproduct3520-
5supplier2product652012.92308
6supplier2product2525-
7supplier2product3020-
8
Sheet1


paddy
 

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
Great,

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

Brett.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top