I need some help with custom rounding in decimals

thill0209

Hi all,

I am trying to find a way to use custom rounding within a formula. I've tried using the AI formula generator, but it ignores that part of my request. I have also searched for help online but no luck.

Essentially I want to create the following custom rounding based on the outcome of a formula -

Between 0.01 and 0.24  -  round down to 0

Between 0.25 and 0.74  -  round to 0.5

Between 0.75 and 0.99  -  round to 1

Any ideas?

Amit Wadhwani

Hi @thill
Assuming that the column that contains your formula is titled "Value", you can use the formula below

=IF(AND(Value@row - FLOOR(Value@row, 1) >= 0.01, Value@row - FLOOR(Value@row, 1) <= 0.24), FLOOR(Value@row, 1),

IF(AND(Value@row - FLOOR(Value@row, 1) >= 0.25, Value@row - FLOOR(Value@row, 1) <= 0.74), FLOOR(Value@row, 1) + 0.5,

IF(AND(Value@row - FLOOR(Value@row, 1) >= 0.75, Value@row - FLOOR(Value@row, 1) <= 0.99), FLOOR(Value@row, 1) + 1, Value@row)))

Explanation:

1. FLOOR(Value@row, 1) extracts the integer part of the value (e.g., for 1.48, it gives 1).
2. Value@row - FLOOR(Value@row, 1) calculates the decimal portion (e.g., 1.48 - 1 = 0.48).
3. The IF logic:
   1. 0.01–0.24 → Round down to the nearest integer (FLOOR(Value@row, 1)).
   2. 0.25–0.74 → Round to 0.5 above the integer (FLOOR(Value@row, 1) + 0.5).
   3. 0.75–0.99 → Round up to the next integer (FLOOR(Value@row, 1) + 1).

Let me know if this helps.