9 Replies Latest reply on Oct 18, 2016 4:16 PM by Flyfisher2611

# Can I round up a calculation field at a specified point, i.e. 0.2

Hi, I have a horticultural nursery and am creating a database to manage my seed sowing etc.

My Question

Can I round up a calculation field at a specified point, i.e. 0.2?

I would like to round up the number of seed trays to be sown, eg from Four to Five, when the result of the calculation is at 4.25! The round function only rounds up above 4.5!

Many thanks in advance

• ###### 1. Re: Can I round up a calculation field at a specified point, i.e. 0.2

I believe you need the Ceiling function.

• ###### 2. Re: Can I round up a calculation field at a specified point, i.e. 0.2

Hi schamblee, Thank you for your response!

I appreciate that the ceiling function rounds up but I dont think it gives me the ability to 'specify' the point at which it will round up!

to explain - If the calculation says I need 1.10 (upto 1.20) trays sowing, I would like the formula to round down to 1 tray. If the result says I need 1.30 (or above) trays sowing I would like the formular to round up to 2.

• ###### 3. Re: Can I round up a calculation field at a specified point, i.e. 0.2

Hi,

you can use Ceiling ( DecimalTrays - .3 )

... a zero result is possible, if the DecimalTrays <= .3

• ###### 4. Re: Can I round up a calculation field at a specified point, i.e. 0.2

ahem, sorry ...

Max(

Ceiling ( DecimalTrays - .2 );

1

)

p.s.  to annotate the above ... the threshold is set at .2 (20% of a single tray).  The subtraction "rounds down" your decimal/rational tray number value, per your specification; and Ceiling "rounds" up to the next integer.  The Max function with the parameter 1 ensures a minimum of one tray.

• ###### 5. Re: Can I round up a calculation field at a specified point, i.e. 0.2

Would this work?:   Round ( .25 +  ( field1 / field2 ) ; 0 )

• ###### 6. Re: Can I round up a calculation field at a specified point, i.e. 0.2

Int ( Trays )  + getAsBoolean ( ( 4 * ( Trays - Int ( Trays ) ) ) ≥ 1 )

• ###### 7. Re: Can I round up a calculation field at a specified point, i.e. 0.2

I think if you have a specific requirement like that you need to construct a specific calc. The best way to do so is in a Let ( ) function, where you can specify whatever parameters you require, for example:

Let (

[

number = <inputFromYourField>

; fraction = Mod ( number ; 1 )

; limit = 0.2

; result = If ( fraction ≤ limit ; Floor ( number ) ; Ceiling ( number ) )

] ;

result

)

This calc extracts the modulus (i.e. the fraction part) and tests that against the set limit, then uses either the Floor or Ceiling function to set the resulting whole number—1.20 would result in 1, anything above that would result in 2. Try it and see if it suits your need.

• ###### 8. Re: Can I round up a calculation field at a specified point, i.e. 0.2

This is great guys, thank you!

ill give them a try and let you know which works best! Thanks again!

• ###### 9. Re: Can I round up a calculation field at a specified point, i.e. 0.2

Hi David

I went for the following calc.

If ( IsEmpty ( Packs Required ) ; 0 ; Max (Ceiling ((Packs Required * Container) / (BKGrowersSeedTray / 100 * Plant Details export file 2016 v1::Percentage Emergance)-.3); 1 ) )

I added  the "if" so the field would return zero, rather than showing One as a minimum number of trays to be sown.

Thank you for your help

Kind Regards

Alan