Formula Help

P

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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
=roundup(a1,0)

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

Hope this helps,
Michael
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
If Formula
Replies
2
Views
73
dreid1011
dreid1011
W
  • Question Question
Dynamic formula to show the day and the number of cycles that can be ran that day
Replies
6
Views
142
Peter_SSs
Peter_SSs
J
  • Solved
Excel Workdays Formula (Specific)
Replies
7
Views
430
felixstraube
F
Edgar_
  • Solved
Array formula
Replies
6
Views
366
StephenCrump
S
JenniferMurphy
  • Question Question
Can someone help me understand Bayesian averages?
Replies
1
Views
173
FormulasOnly
F

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,013
Latest member
Shubashish_Nandy

Share this page

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
Back
Top