Ensure numeric display always has 2 decimal places

Question

Good Day,

I have price and spread values. Price is numeric, while Spread can be +1, +0.5, -0.5, -1 or "Flat" aka 0

My task is to put the 2 values together.

E.g. Price = 600

I need the final result to always show 2 decimal places, even if they are .00

And it cannot round up or down. But my .00 are always trimmed off.

What I always get:

Any advise is appreciated. Thank you.

Answer 1

You could do this with a formula:

Assuming that the price is stored in a named range Price and your data is stored in column A, starting in row 2:

=TEXT(B2,"0.00")&" ("&IF(B2-PRICE=0,"Flat",ROUND(B2-PRICE,2))&")"

Explanation:

The TEXT-function converts a number into a formatted string, giving a format pattern. '0.00' will format the value with 2 decimals - that gives you for example 600.00 or 599.95

TEXT(B2,"0.00")

Now we use the IF-function to check if the value is equal to PRICE or different. If it is equal to PRICE, we write the string Flat, else we calculate the difference. We need to use the ROUND-function because else we can get a difference of 0.00999999 instead of 0.01 because of floating point math.

IF(B2-PRICE=0,"Flat",ROUND(B2-PRICE,2)

The rest is string concatenation