ROUNDDOWN In Cell Format ONLY?

[Centrican]

Hey Guys,

Another bizzarre request from me:

I'm looking for a way to apply a rounddown function to values in cells, but to their format only, not the number itself.

e.g. I'm wanting to be able to enter 1.4 and 1.7, have both display as 1, yet add together to display 3.

I've tried everything I can think of to solve this, I'm starting to wonder if it is even possible.

Anyone got any thoughts about how I can achieve it?

 

[njimack]

Just format it as a Number to 0 decimal places

 

[Centrican]

If it's 1.5 or above it rounds up. I want 1.5 to display as 1.

 

[Jonmo1]

Don't think you can do that with just formatting.
You need a formula in an adjescent column to do the round down.

 

[shg]

Agree with jonmo.

 

[Jonmo1]

Only possibility I can think of is IF (big IF), your numbers will never be larger than 31

Then use custom format of
d

 

[jasonb75]

You can do it, but only with values less than 32.

edit:didn't refresh, jonmo had same thought.

 

[Jonmo1]

You can do it, but only with values less than 32.

edit:didn't refresh, jonmo had same thought.

But you're more accurate..

I said no larger than 31...
But Less than 32 is more accurate...

It will work for 31.9999


It's probably a Moot point anyway...

 

[shg]

Still a clever idea.

 

[Scott Huish]

It can also be done with VBA.