Formula Help

[paul29berks]

I have products that are split by class and dealer for each country for 2004 (actual sales results) and based on a % load for each dealer trying to project for 2005 as only have total volume for Product.

For Example

2004 Actual Results

Germany Class1
Total Class 1
Dealer 1 - Product A = 0 Product B = 1 1
Dealer 2 - Product A = 0 Product B = 2 2
Dealer 3 - Product A = 0 Product B = 3 3

2005 Plan

Germany
Class 1

Total Product A = 1 Product B = 6

so for Dealer 1 and Product B in Germany

= Germany Class 1 2004 Actual results dealer 1 Product B/Total Product B * Total Product B 2005 = 1

For Product A I have 0 for 2004 and total for 2005 of 1 -Can I write a formula which states:

if 2004 Actual results Germay dealer 1 Product A/total product A * Total Product A 2005 = 0 then calculate total for class 1 dealer (i.e Product A + Product B)/total class 1 2004 * product A 2005?

Many Thanks

 

[paul29berks]

Thanks

So i could include in formula as:

=IF(AND($D$78=0,$Z$78=0),CH$78*($Z67+$AP67)/($Z$78+$AP$78),IF(AT$78=0,CH$78*($BN$67+$CD$58)/($BN$78+$CD$78),CH$78*($BN67/$BN$78), roundup,0)

 

[paul29berks]

If i had no figures for 2003 or 2004 and for 2005 a value of 1 for example receive a divide by 0 error how can i avoid this?

 

[Fergus]

Hi Paul,

To answer both your questions i.e. (1) rounding up to whole number and (2) what if zero totals for 2003 & 2004 as well as your question in: http://www.mrexcel.com/board2/viewtopic.php?t=126889

and using the same layout as I used before, change the formula in B13 to :
=IF(AND($B$6=0,$C$6=0,$B$11=0,$C$11=0),1,IF(AND($B$11=0,$C$11=0),ROUNDUP(B$12*($B3+$C3)/($B$6+$C$6),0),IF(B$11=0,ROUNDUP(B$12*($B8+$C8)/($B$11+$C$11),0),ROUNDUP(B$12*(B8/B$11),0))))

You can copy this down and across.

This will give a result of 1 if zero totals for 2003 & 2004. Using ISERROR to trap a #DIV! is not a good idea as it will trap ALL errors and thus maybe mask another error which you need to know about. Only use ISERROR if you are absolutely sure that you have no other errors.

HTH