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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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.
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
Many Thanks for your reply.

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?
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
Re: Formula Help - Divide By Zero Error

How can I prevent #DIV/0 error appearing?

Many thanks
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174

ADVERTISEMENT

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
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
Can anyone advise:

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?
 

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293

ADVERTISEMENT

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

Many thanks
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
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
 

paul29berks

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

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
=roundup(a1,0)

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

Hope this helps,
Michael
 

Forum statistics

Threads
1,147,845
Messages
5,743,513
Members
423,801
Latest member
paulj4177

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