Percentage Allocation Variance

[ExcelNewbie2020]

Sirs,
In column E, the total number is 65 but if you will going to sum it manually its only 64. I know its due to the decimal because of 65/170 of 38%.

My question is, is there a function/formula like rounding up/down that i can use that even there is a decimal it can still result to 65?.. Also the 65/170 is changing regularly.. thanks

Book1
	C	D	E	F
2	38%	Allocation Percentage (65/170)
3
4		QTY	Allocation (QTY* (65/170)
5	Tyson Sullivan	2	1
6	Neo Bassett	40	15
7	Gilbert Peck	27	10
8	Harlee Bonner	25	10
9	Bryce Evans	3	1
10	Howard George	3	1
11	Winifred Bloggs	10	4
12	Fionn Bate	3	1
13	Zayan Waller	9	3
14	Emaan Vinson	8	3
15	Nazim Adkins	8	3
16	Lee Mayo	4	2
17	Richie O'Doherty	11	4
18	Kieron Wharton	14	5
19	Benedict Hinton	3	1
20	Total	170	65
Sheet1

Cell Formulas
Range	Formula
C2	C2	=65/170
E5:E19	E5	=D5*$C$2
D20:E20	D20	=SUM(D5:D19)

 

[Joe4]

In order for the total shown to match each of the values, you should use the ROUND formula in your function.
Even though you aren't showing the decimal portion of each calculation, it is still there (even though you cannot see it), and is being included in the total.

So you should use the formula:

=ROUND(D5*$C$2,0)

in column E instead.
If you do that, it will correctly return the total for column E of 64 (as that is what each of those individual numbers add up to).

 

[ExcelNewbie2020]

In order for the total shown to match each of the values, you should use the ROUND formula in your function.
Even though you aren't showing the decimal portion of each calculation, it is still there (even though you cannot see it), and is being included in the total.

So you should use the formula:

=ROUND(D5*$C$2,0)

in column E instead.
If you do that, it will correctly return the total for column E of 64 (as that is what each of those individual numbers add up to).

thank you for the response.. the total i am looking for is also 65, either manually or by using sum function..

 

[Joe4]

Then you will need to add 1 to one of the individual records listed.
How you decide which one to add 1 to is up to you.

If you wanted to do it with a formula, you could replace the last formula in column E, i.e.
in cell E19, change the formula to this:

=ROUND(D20*C$2$,0)-SUM(E5:E18)

That will change the value in cell E19 from 1 to 2, and now your total will be 65.

 

[ExcelNewbie2020]

Then you will need to add 1 to one of the individual records listed.
How you decide which one to add 1 to is up to you.

If you wanted to do it with a formula, you could replace the last formula in column E, i.e.
in cell E19, change the formula to this:

=ROUND(D20*C$2$,0)-SUM(E5:E18)

That will change the value in cell E19 from 1 to 2, and now your total will be 65.

thanks... however, as you can see, there are 4 names that have a qty of 3. increasing the last name may get conflict with the others.. i got your idea to increase by 1 and already tried that.. i am looking for a dynamic formula (if possible)so i can avoid the manual increase..

 

[Joe4]

thanks... however, as you can see, there are 4 names that have a qty of 3. increasing the last name may get conflict with the others.. i got your idea to increase by 1 and already tried that.. i am looking for a dynamic formula (if possible)so i can avoid the manual increase..

You didn't like the formula I gave you?

If not, you need to explain to us the logic for exactly how it should be determined which record to adjust.

 

[ExcelNewbie2020]

You didn't like the formula I gave you?

If not, you need to explain to us the logic for exactly how it should be determined which record to adjust.

thanks man, really appreciated..I don't know if its possible, the idea is to apply the same formula on E5:E19 that is somehow the total will be at 65 (manually or by sum) then they will look equally distributed based on percentage.

 

[Joe4]

thanks man, really appreciated..I don't know if its possible, the idea is to apply the same formula on E5:E19 that is somehow the total will be at 65 (manually or by sum) then they will look equally distributed based on percentage.

That is not possible, especially since you are rounding the values!

For a simple demonstration, consider this example:
- Split 10 equally among 3 people, usually only whole numbers (no decimals).
It cannot be done! The closest you can get is 3, 3, and 4.

This is exactly the problem you are facing.
You need some method of determining who to give (or take) the "extra" one to.
If it doesn't matter, and can be random, then the formula I gave you already does EXACTLY that (the last person in the list is the one who is adjusted).

If it does matter which one needs to be adjusted, then YOU need to tell us how we determine which one to give (or take) the extra one to in order to make it equal the original number.
We need to know the logic you want to use to determine exactly which one should be adjusted.