Formula Question

[brettvba]

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
	A	B	C	D
2	Supplier	Product	TotalInStock	AverageSale/Week
3	Supplier	Product	TotalInStock	AverageSale/Week
4	Supplier	Product	TotalInStock	AverageSale/Week
5	Supplier1	Product	TotalInStock	AverageSale/Week
6	Supplier1	Product	TotalInStock	AverageSale/Week
7	Supplier2	Product	TotalInStock	AverageSale/Week
8	Supplier2	Product	TotalInStock	AverageSale/Week
Sheet1

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

 

[PaddyD]

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]

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]

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

Paddy

 

[brettvba]

Ok this is one supplier worked out, I have about 1200 suppliers

Book1
	A	B	C	D	E
1			Quantityonhand	AverageWeeklySale	DaysCover
2	Supplier	Product	55	10
3	Supplier	Product	45	15
4	Supplier	Product	35	20
5		Totals	135	45	21
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]

with no thought to optimisation, and with a master list of the appropriate sort:

Book7
	A	B	C	D
1	Sup	Prod	Num	Ave
2	supplier	Product	55	10
3	supplier	Product	45	15
4	supplier	Product	35	20
5	supplier2	Product2	65	20
6	supplier2	Product2	25	25
7	supplier2	Product3	30	20
8
9
10	IfonlychangesinSuppliermatter:
11
12	supplier	21
13	supplier2	12.92308
14
15	IfchangesinSupplier&ProductMatter
16
17	supplier	Product	21
18	supplier2	Product2	14
19	supplier2	Product3	10.5
20
21
Sheet1

Paddy

 

[brettvba]

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]

assuming colA is sorted by supplier:

Book6
	A	B	C	D	E
1	Sup	Prod	Num	Ave
2	supplier	product	55	10	21
3	supplier	product	45	15	-
4	supplier	product	35	20	-
5	supplier2	product	65	20	12.92308
6	supplier2	product	25	25	-
7	supplier2	product	30	20	-
8
Sheet1

paddy

 

[brettvba]

Great,

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

Brett.