Re-distributing numbers against a total value.

[DazCD]

Good morning, hopefully the title makes sense but I'll try my best to explain:

Basically, I have a 1KG bag of mixed sweets, the 1KG bag is made up of several components.

I'm pulling the information from our ERP system for the 'quantities' (What these quantities are based upon, I don't know - It's just the information I have)

So, I have the following Bill of Materials:

Component	QTY	Description	Formula	Formula result	Final QTY
N92381	0.7	Green Paint Ball Mallows 900g	=0.9*0.7	0.63	10% (0.63/6.033)*100
N94137	0.525	Jelly Beans 3kg	=3*0.525	1.575	26% (1.575/6.033)*100
N94413	0.473	Gingerbread Men Flavoured Candy 3kg	=3*0.473	1.419	24% (1.419/6.033)*100)
N94423	0.473	Soft Candy Canes Mini 3kg	=3*0.473	1.419	24% (1.419/6.033)*100)
N99003	0.33	Fizzy Christmas Trees Red & Green 3kg	=3*0.33	0.99	16% (0.99/6.033)*100
6.033​	100%​

To calculate the quantities for each item, I'm multiplying the size of the bag (from the description) against the QTY (Col B) using the formula in Col D, The results are as Col E.

In Col F, the 'Final QTY', This is calculated by dividing each result (Col E), by the total sum of the values in Col E, (6.033 being the total SUM), which is then expressed as a percentage.

The issue I'm having is that I want these % values to be expressed to to the nearest multiple of 5 whilst still equaling 100. I've tried =CEILING & ROUND formulas to try it but I can't figure it out.

So my final %'s might look look like:

10%

25%

25%

25%

15%

100%

Thank you in advance for any replies.

 

[DazCD]

I don't know why but it's put the figures in my table in the incorrect place, the

6.033

100%

These should be underneath Col E & F respectivley.

 

[myall_blues]

How about this formula?

Book1
	A	B
1	Final QTY	Rounded value
2	10	10
3	26	25
4	24	25
5	24	25
6	16	15
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=INT((A2/5)+0.5)*5

 

[hagia_sofia]

Hello, what would be the rule if there are two same values that are supposed to be rounded down (22 %), i.e. the result would then be 95% instead of 100%?

 

[myall_blues]

@hagia_sofia it nearly always works out. For example with 22, 22 the other 3 numbers would sum to 56, so say 18, 19, 19. In that case the rounding will leave 5x20=100.

 

[hagia_sofia]

@myall_blues that is correct but @DazCD maybe could have some rule if it happens...

 

[DazCD]

I've tried some testing using your formula @myall_blues, it appears to work - absolutely fantastic, thank you!

As for a rule for the values not adding up to 100, I don't know, I'm not amazing at Math's but I'll cross that bridge should it occur.