# Formula Help

#### paul29berks

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

Replies
0
Views
184
Replies
8
Views
110
Replies
1
Views
92
Replies
4
Views
153
Replies
0
Views
103

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

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.

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