New Help - Quick

[mk1019]

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!

 

[Richie(UK)]

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

 

[mk1019]

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

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]

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

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]

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