Formula Help

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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

Active Member
Joined
Mar 15, 2004
Messages
293
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)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
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

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,573
Messages
5,765,182
Members
425,266
Latest member
CPAgirl

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