rounding problem in vba

[TheDude111]

hey guys

Im using excel 2011,

basically what Im doing is taking a product quantity and dividing it among customer based on a percentage.

e.g. divide 10 apples between 2 customers A and B with A getting 70% and B getting 30 % etc.

However some products will only get sold in groups of 4,6,8 etc.

In my formula this is what is in column AP


I was using application.round which works for the majority of cases in my formula.

Code:

  <style>.alt2 font { font: 11px monospace ! important; color: rgb(51, 51, 51) ! important; }</style>  
[FONT=Arial][SIZE=2][COLOR=#000000]While (Range("A" & loopCounter4) <> "")

        If (Range("E" & loopCounter4) = "A") And (Range("G" & loopCounter4) = "1") Then
              Range("AC" & loopCounter4) = Application.Round(((Range("AB" & loopCounter4) / Range("AP" & loopCounter4))) * 0.3, 0) * Range("AP" & loopCounter4)
            
        ElseIf (Range("E" & loopCounter4) = "B") And (Range("G" & loopCounter4) = "1") Then
                Range("AC" & loopCounter4) = Application.Round(((Range("AB" & loopCounter4) / Range("AP" & loopCounter4))) * 0.7, 0) * Range("AP" & loopCounter4)[/COLOR][/SIZE][/FONT]

There are cases however when i end up with a ".5" for each and both are rounded up. Which leaves me with more than the original figure.

I tried using a rounding function

Code:

  <table class="tborder" id="post2570431" align="center" border="0" cellpadding="6" cellspacing="0" width="100%"><tbody><tr><td class="alt1" id="td_post_2570431"><style>.alt2 font { font: 11px monospace ! important; color: rgb(51, 51, 51) ! important; }</style>  
[FONT=Arial][SIZE=2][COLOR=#000000]Function RoundSpecial(pValue As Double) As Double

Dim LWhole As Long

Dim LFraction As Double

'Retrieve the interger
LWhole = Int(pValue)

'Retrieve the fraction
LFraction = pValue - LWhole

If LFraction < 0.5 Then
    CustomRound = LWhole
Else
    CustomRound = LWhole + 0.5

End If

End Function[/COLOR][/SIZE][/FONT]

Any help or ideas would be appreciated.</td></tr></tbody></table>

 

[TheDude111]

anyone

 

[MARK858]

Have a look to see if you can incorporable the "Ceiling" function.

 

[xenou]

There are cases however when i end up with a ".5" for each and both are rounded up. Which leaves me with more than the original figure.

What do you actually end up with? Is it always 1.5, 2.5, 3.5, 4.5 ... like that? Are the numbers evenly spread or is it a preponderance of, say, a lot of 4.5 and never any 3.5? I ask because VBA uses "Banker's Rounding" ... I'm not sure how this applies in your case though.

Here's a link (see "other forms of rounding" for banker's rounding explained ...):
http://www.consultdmw.com/rounding-numbers.htm

 

[TheDude111]

What do you actually end up with? Is it always 1.5, 2.5, 3.5, 4.5 ... like that? Are the numbers evenly spread or is it a preponderance of, say, a lot of 4.5 and never any 3.5? I ask because VBA uses "Banker's Rounding" ... I'm not sure how this applies in your case though.

Here's a link (see "other forms of rounding" for banker's rounding explained ...):
http://www.consultdmw.com/rounding-numbers.htm

Basically if i end up in the situation for example

100 being split 70% to customer A and 30% to customer B would be 70 and 30 except if the if the 100apples had to ship in a pack of 12's

so it based on my fourmulawould be ((100/12) *.3) = 2.5.. will round to 3
and ((100/12)*.7) = 5.83 will round to 6

and when you multiply back by the pack sizes you get 36 and 72 which gives you 108 apples which is more than i had to begin with...

basically i want to make sure that the 70% will round up and the 30% down if both go over .5. Instead of 2.5 and 5.83 i need it to go to 2.0 and 5.83... giving me 96 apples

 

[TheDude111]

Basically if i end up in the situation for example

100 being split 70% to customer A and 30% to customer B would be 70 and 30 except if the if the 100apples had to ship in a pack of 12's

so it based on my fourmulawould be ((100/12) *.3) = 2.5.. will round to 3
and ((100/12)*.7) = 5.83 will round to 6

and when you multiply back by the pack sizes you get 36 and 72 which gives you 108 apples which is more than i had to begin with...

basically i want to make sure that the 70% will round up and the 30% down if both go over .5. Instead of 2.5 and 5.83 i need it to go to 2.0 and 5.83... giving me 96 apples

Sorry its late thats a bad example the rounding will work in that case

but for 200 apples

200 applesbeing split 70% to customer A and 30% to customer B would be 140 and 60 except if the if the 100apples had to ship in a pack of 12's

so it based on my fourmulawould be ((200/12) *.3) = 5.. will round to 6
and ((200/12)*.7) = 11.67 will round to 12

and when you multiply back by the pack sizes you get 72 and 144 which gives you 216apples which is more than i had to begin with

Basically in this case i would need the 5 to stay as 5 and the 11.67 to round to 11... giving me 192 apples

 

[xenou]

I might need to see how you're structuring your analysis as a whole. 200 apples seems to artificial --> the leftover 8 apples is misleading. If you had 200,000 apples being sold (more realistically) and you had a leftover 8 apples it's insignificant and you could stick with the 70/30 split. Or you could split 70/30 on the pack count.

Why not just use the decimals - if customer A gets (on average) 70% then call a spade a spade. They get 11.67 packs. How is that a problem since this is analysis and not actual shipping of partial packs?

That said, usually I work a problem like this by plugging the final number - so if A is 11.67 rounding to 12, then B is (16 - 12) = 8.

ξ

 

[TheDude111]

I might need to see how you're structuring your analysis as a whole. 200 apples seems to artificial --> the leftover 8 apples is misleading. If you had 200,000 apples being sold (more realistically) and you had a leftover 8 apples it's insignificant and you could stick with the 70/30 split. Or you could split 70/30 on the pack count.

Why not just use the decimals - if customer A gets (on average) 70% then call a spade a spade. They get 11.67 packs. How is that a problem since this is analysis and not actual shipping of partial packs?

That said, usually I work a problem like this by plugging the final number - so if A is 11.67 rounding to 12, then B is (16 - 12) = 8.

ξ

The problem is this is something I am working on for work. So i cant have partial packs. I have to make sure it works 100%. With the bankers rounding going to an even number it either over assigns stock or splits into something less than the pack size. So the left over 8 is ok so long as the rest is in 12's etc

 

[GlennUK]

Then do the calculations on packs, rounding down the calculation of total number of packs in the beginning:

Excel Workbook
	A	B	C
1	pack size	12
2	apples	200
3	available packs	16
4	customer A	70%	11
5	customer B	30%	5
Sheet1

 

[TheDude111]

Then do the calculations on packs, rounding down the calculation of total number of packs in the beginning:

Excel Workbook
	A	B	C
1	pack size	12	*
2	apples	200	*
3	available packs	16	*
4	customer A	70%	11
5	customer B	30%	5
Sheet1

Hey Glenn

Thanks for the reply, is there a quick way to apply this to the code, i gave one example but there a 100's of different customers all with different % splits.