Rounding number but not as you would expect

Hi again. Hoping you can all be as useful as last time.

Basically each day we get orders for products that are boxed and put on pallets. I have written basic formulas that tell be based on orders how many pallets I need. The problem is I can only order pallets to be delivered in whole or halves.

So my question is:

is there a function to allow me to round anything from .01-.59 to 0.5 and anything from .6 or above to the next whole number.

It has to be from 0.6 as this represent what the haulage company deems to be a half pallet. If the solution involves vba please can you be quite detailed in how to achieve this.

Many thanks in advance

Hmmmm, I don't think that will work due to .51-.59 rounding up to next whole number as opposed to .5.

Unless u can tweak ceiling function that is.

Basically need formula to compute that if 0.01-0.59 number = 0.5
But if greater than 0.59 then number equals next whole number

I cud be wrong and ceiling does that but from reading it doesn't appear to

Hmmmm, I don't think that will work due to .51-.59 rounding up to next whole number as opposed to .5.

Unless u can tweak ceiling function that is.

Basically need formula to compute that if 0.01-0.59 number = 0.5
But if greater than 0.59 then number equals next whole number

I cud be wrong and ceiling does that but from reading it doesn't appear to

Hmmmm, I don't think that will work due to .51-.59 rounding up to next whole number as opposed to .5.

Unless u can tweak ceiling function that is. Tested it as basic function and doesn't appear to work. Is it possible perhaps to use an if formula where rounds down if below certain figure (.59 to .5) and if greater than .60 rounds up

Basically need formula to compute that if 0.01-0.59 number = 0.5
But if greater than 0.59 then number equals next whole number

Originally Posted by ChemistB

No simple function since you are not symetrically rounding (60% of values between 1 and 2 are rounded to 1.5)

Try this formula
=IF(MOD(A1,1)<=0.594, INT(A1)+ 0.5, ROUND(A1,0))
Does that work for you?

This seems to nearly achieve what I'm looking for. The problem with this formula is that If I calculate I need exactly, for example, 2 pallets it rounds this up to 2.5. Does the expression need tweaking to state between 0.01 and 0.594. Your help would be massively appreciated on this tweak.

A second issue which is my fault for not specifying it earlier and may be a jump too far but .... If the calculation for pallets returns 0.25 you wud assume half a pallet. However if ordering less than a pallet u have to pay for whole pallet. So expression above with tweak will work for pallets over 1 but is there away of saying if result is less than 1 then round up to 1 but if higher than 1 the above expression applies.

Ie. 0.22 would be 1 pallet
0.62 would be 1 pallet
1.22 would be 1.5 pallets
1.62 would be 2 pallets.

Many thanks

Originally Posted by ChemistB

=IF(AND(MOD(A1,1)<=0.594,MOD(A1,1)>=0.01), INT(A1)+ 0.5, ROUND(A1,0))
For the second part, we need to expand the formula futher
=IF(INT(A1)<1, 1, IF(AND(MOD(A1,1)<=0.594,MOD(A1,1)>=0.01), INT(A1)+ 0.5, ROUND(A1,0)))

Would that work for you?

Works a dream with one exception. If a1 is left blank it returns a 1 for numbers of pallets required as opposed to blank or 0.

Any ideas on that one. Thank you so much so far. Never would have got this.

Brilliant. Thank you so much. Works like a dream and doesn't seems to create any errors.

Problems solved ....... Hours of futile trying sidestepped. Thanks again.

Apologies I'm getting greedy for information now but I was just wondering in addition to the above if I had another cell that would warn the person filling this data in to check the pallet configuration if the pallet was only just another a certain number of pallets or just under.

For example if the pallet configuration was 6.99 so rounded to 7 a warning would come up in separate cell Stating "check no of pallets correct". And the same warning if it was just over ie 6.05. Say have the warning come up if within 0.05 of .5 or a whole number.

Sorry if badly explained.

Originally Posted by ChemistB

So the actual number would be the same number that we were using in the previous formulas (i.e. A1)?

=IF(OR(INT(A1)< INT(A1+0.05), INT(A1)>INT(A1-0.05)), "Check Pallet Numbers", "")

Hi. This works perfectly on the whole pallet calculation ie 2 pallets or 3 pallets but not if I calculate I need 3 and a half pallets ordering. Is there a way to tweak the formula to factor this in.

Cheers

Originally Posted by ChemistB

Okay, try this one (the previous one would have failed on exact pallet matches anyway)

=IF(OR(AND(MOD(A1,0.5)>0, MOD(A1,0.5)<= 0.05), MOD(A1,0.5)>= 0.45),"Check Pallet Count","")

Very Close....

Only problem with this forumla is that it doesnt factor in the whole numbers like the previous formula.
Probably havent been very clear so now at a comp rather than on phone i can expand.

Looking for a "Check Pallet Count" to appear if A1:

is between 0.95 and 1.05
1.45 and 1.55
1.95 and 2.05 and so on

No need it to give a warning if the pallet load is between 0.45 and 0.55 as the first pallet is always charged as 1.

Hope this makes things cleaer (but im guessing slightly more complicated)

Originally Posted by ChemistB

I don't think you checked it. Take a look at this example

Cheers for spreadsheet.
Problems are if u change a1 to 0.45 it will say check pallet where no need as anything under 1 will be deemed 1 pallet. only over 1 can we have half pallets so first wanring should only be triggered at 0.95.
Second problem is a6 equals 1.5 but has no warning come up. ANything between 1.45 and 1.55 needs a warning to check that the load can actually fit on the pallet amount specified. Other than that the forumla is working

Originally Posted by daddylonglegs

Doesn't it still have to return "Check Pallet Count" for whole numbers of pallets like 2 or 3? Perhaps try this version

=IF(AND(A1>=0.95,ABS(MROUND(A1,0.5)-A1)<=0.05),"Check Pallet Count","")

Brilliant. Thank you both very much. THis one worked a treat for what i required.