New Help - Quick

mk1019

New Member
Joined
Aug 30, 2002
Messages
7
I am trying to figure out a way to allocate a number. I am sure this question has been asked, but I dont even know how to begin searching for it.

I have a value of 50 that I need spread across 12 months. Each month has a different percentage value.

The result per month needs to be a whole number...and the total calculation needs to come to 50.

As it sits, I have the following (using =ROUND(50*Pct,2):

Month Pct Total
Jan 11 6
Feb 5 3
Mar 15 7
Apr 11 6
May 12 6
Jun 8 4
Jul 8 4
Aug 5 3
Sep 3 2
Oct 3 2
Nov 9 4
Dec 9 4
The issue is that the result comes to 51 and I need it to come back to 50. Plus, when I put in a totaler account, it comes to 50 because Excel reads the true value and not the rounded number on the screen).

I am sure that this is a simple fix, but I am brain dead at this point.

Can someone pleae help?!

Thanks!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi mk1019,

I tried your example, except I used 0.11 rather than 11 which is what I think you meant to type, and I had no trouble getting exactly 50.

(Apart, that is, from the fact that the percentage figures you provided actually come to 99 rather than 100 :wink: - I changed the last figure to a 10 rather than a 9)

Am I missing something?


_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

caffeine_sample.gif
</gif>
This message was edited by Richie(UK) on 2002-08-31 10:01
 

mk1019

New Member
Joined
Aug 30, 2002
Messages
7
The percents I listed were rounded. The actual percents in the calculation are the result of Data A / Data B. That answer is then multiplied by 50.

For example, in January, the percent formula is 13/115. This gives a percent of 11.3044.

Therefore, the 50 total X .113044 is 5.6522.

The process is repeated for each month, with its respective percentage.

My qunadry is that I need Excel to return a whole number for each month (which is easy), and the total of all months must equal 50 (where I am stuck).
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi,

How about a lazy person's answer? :)

= Make your December calculation equal to 50 less the sum of the figures for January to November.

Any good, or do you have to do it properly?
 

mk1019

New Member
Joined
Aug 30, 2002
Messages
7

ADVERTISEMENT

Very good concept!! Thanks much!

Do I have to do it properly? Nah...not really...it's just a budget! hehe

Would there be a way to do what you were saying, but rather than picking December, have the formula pick the month with the highest percentage? I wonder how rank would come into play??

Thoughts anyone?!
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi

How about:

=IF(MAX(A1:A3)=A3,"Cheat here!","Normal calc here!")

this is the formula in cell B3. It looks for the Max figure in the range A1:A3, if its the max do one thing, if its not then do something else. Who says cheats never prosper? :wink:
 

mk1019

New Member
Joined
Aug 30, 2002
Messages
7
While that is a good idea, what if you have two or more months that have the same value. The formula will then calculate twice.

Hmmm......
 

Forum statistics

Threads
1,144,274
Messages
5,723,441
Members
422,497
Latest member
dougy99

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