# 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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

Something like :
Book1
ABCD
1ProductAProductB
2ActualforYear2004
3Dealer1011
4Dealer2022
5Dealer3033
6Totalsfor2004066
7PlannedTotalsforYear200516
8Dealer10.166666671
9Dealer20.166666672
10Dealer30.166666673
Sheet1

Formula in B8 is : =IF(OR(B3=0,B\$6=0),(\$B\$3+\$C\$3)/(\$B\$6+\$C\$6)*\$B\$7,(B3/B\$6)*B\$7)
Which can be copied down and across.

I have noticed that using this formula - if I have no units at all for dealers 1 - 3 - the same volume is copied down. So if I had total 2005 of 1 I end up with 3.

Can you help?

Re: Formula Help - Divide By Zero Error

How can I prevent #DIV/0 error appearing?

Many thanks

Hi Paul,

Having checked my last post I find it wasn't giving the correct result anyway, sorry about that it was done in haste without checking. I have had another go and it looks like this:
Book1
ABCD
1ProductAProductB
2ActualforYear2004
3Dealer1011
4Dealer2022
5Dealer3033
6Totalsfor2004066
7PlannedTotalsforYear200516
8Dealer10.1666666671
9Dealer20.3333333332
10Dealer30.53
11CheckTotalForecast16
Sheet1

Where the formula in B8 is : =IF(B\$6=0,B\$7*(\$B3+\$C3)/(\$B\$6+\$C\$6),B\$7*(B3/B\$6)) which can be copied down and across.

However, this will not work if all Dealers scored zero on both Products in 2004 because then there is no basis upon which to apportion the forecasts. The only way would be to allocate each Dealer an equal amount but then you would have to first count how many Dealers you have and divide your forecast by the number of Dealers.

HTH

Have the following formula:

=IF(OR(AS16=0,AS20=0),IF(\$BN16,(\$BN20)/(\$BN20),0),(\$AR\$16/\$AR\$20))*CG20

if all cells are 0 and I am mulitplying by cell CG20 which is 1 - then I will receive value 0 - what if I wanted to show value as 1?

If I was to include 2003 results same layout, how could I write this into my formula?

Many thanks

Hi Paul,

Is this what you mean?
Forecasts.xls
ABCD
1ProductAProductB
2ActualforYear2003
3Dealer102
4Dealer200
5Dealer302
6Totalsfor200304
7ActualforYear2004
8Dealer100
9Dealer200
10Dealer300
11Totalsfor200400
12PlannedTotalsforYear200516
13Dealer10.53
14Dealer200
15Dealer30.53
16CheckTotalForecast16
Sheet1

Formula in B13 is : =IF(AND(\$B\$11=0,\$C\$11=0),B\$12*(\$B3+\$C3)/(\$B\$6+\$C\$6),IF(B\$11=0,B\$12*(\$B8+\$C8)/(\$B\$11+\$C\$11),B\$12*(B8/B\$11)))

As before, this will not work if ALL totals for 2003 & 2004 are zero.

HTH

Thats great!!!

a quick question if i produce an output of say .021 is there a way I can round up to the nearest whole value i.e 1?

=roundup(a1,0)

This will work if a1 contains .21 then you would get a result of 1

Hope this helps,
Michael

Replies
6
Views
240
Replies
4
Views
222
Replies
5
Views
143
Replies
5
Views
157
Replies
1
Views
154

1,220,987
Messages
6,157,233
Members
451,407
Latest member
vdaesety

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