# New Help - Quick

#### mk1019

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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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>

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

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).

Hi,

= 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?

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?!

Hi

=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:

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

Replies
2
Views
146
Replies
5
Views
240
Replies
3
Views
110
Replies
3
Views
194
Replies
1
Views
363

1,221,153
Messages
6,158,235
Members
451,477
Latest member
CWebbers

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