If i've got a column of values where some include pennies and some are just whole pounds, is there a way that i can format the column so that whole pounds show to no decimal places but the lines with pennies still show to two decimals? I know i could do it with an IF statement but i wondered if there was a simpler way?
Formatting numbers
- Thread starter Rachel
- Start date
In 2007 or later, you might be able to do that with a conditional format (not sure if they added this, can anyone confirm/deny?). If so, you just need to check if a number has decimals like this:
=MOD(A2,1)<>0
Beyond that, I think that scenario is beyond the basic conditions you can do with a custom number format. Just for note for other users, the formula would be something like:
=IF(MOD(A2,1)=0,TEXT(A2,"$#,##0"),TEXT(A2,"$#,##0.00"))
The problem is that this results in a text output, not a number. Personally, I think it would look cleaner and better aligned to just have all the numbers display 2 decimals.
=MOD(A2,1)<>0
Beyond that, I think that scenario is beyond the basic conditions you can do with a custom number format. Just for note for other users, the formula would be something like:
=IF(MOD(A2,1)=0,TEXT(A2,"$#,##0"),TEXT(A2,"$#,##0.00"))
The problem is that this results in a text output, not a number. Personally, I think it would look cleaner and better aligned to just have all the numbers display 2 decimals.