changing decimal places according to formula result

[Valeria]

Dear experts,
I have a table which looks up values from a database; this table is linked
to a cell where I can select what I want to see in the table.
Now, sometimes the values I want to see need to be in a 2 decimal format
(ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000). It
really all depends on their magnitude - when <10 then I need to have 2
decimals, if > 10 then no decimal is needed.
Is there a way to do this in Excel 2003?
Many thanks for your help!
Kind regards

 

[Pete_UK]

Suppose your number is in A1. Try this in B1:

=TEXT(A1,IF(A1>10,"0",0.00"))

This will return a text value, and it will round the number, so if
your number is 10.6 it will show 11.

If you don't want this, then here's an alternative:

=IF(A1>10,INT(A1),A1)

Format the cell as General. This time 10.6 will show as 10, and 9.95
will show as 9.95.

If you want this to happen to your cell which contains the VLOOKUP
formula, then just substitute your formula for A1 in the above.

Hope this helps.

Pete

 

[David Biddulph]

Format Cell/ Number/ Custom
[<10]0.00; 0

You talked about <10 and >10, but I guessed that you meant >=10.

 

[MS-Exl-Learner]

Try this formula

=IF(A1="","",IF(A1=T(A1),"",IF(A1<10,TEXT(A1,"#.##"),TEXT(A1,"#######"))))

Type your value in a1 cell. for applying the same formula to your cell
change the reference a1 to your cell.

If this post helps, Click yes...

 

[David Biddulph]

You may want to think a bit further.
As -20 is less than 10, do you want that to show 2 decimals?
If what you intended was 2 decimals for numbers > -10 and < 10, you may want
[<=-10]-0;[<10]0.00; 0
--
David Biddulph

Format Cell/ Number/ Custom
[<10]0.00; 0

You talked about <10 and >10, but I guessed that you meant >=10.
--
David Biddulph

Dear experts,
I have a table which looks up values from a database; this table is
linked
to a cell where I can select what I want to see in the table.
Now, sometimes the values I want to see need to be in a 2 decimal format
(ex. 2.43) and sometimes they need to have no decimals at all (ex.
25000). It
really all depends on their magnitude - when <10 then I need to have 2
decimals, if > 10 then no decimal is needed.
Is there a way to do this in Excel 2003?
Many thanks for your help!
Kind regards

 

[Valeria]

This works really great and is really easy to do!!! Many thanks.
Kind regards
--
Valeria

Format Cell/ Number/ Custom
[<10]0.00; 0

You talked about <10 and >10, but I guessed that you meant >=10.
--
David Biddulph

Dear experts,
I have a table which looks up values from a database; this table is linked
to a cell where I can select what I want to see in the table.
Now, sometimes the values I want to see need to be in a 2 decimal format
(ex. 2.43) and sometimes they need to have no decimals at all (ex. 25000).
It
really all depends on their magnitude - when <10 then I need to have 2
decimals, if > 10 then no decimal is needed.
Is there a way to do this in Excel 2003?
Many thanks for your help!
Kind regards

 

[gwen]

This is a great help to me. However I tried to modify it:

[<=-10]-0;[<10]0.00; [<100]0.0; 0

And Excel won't accept the number format. Did I do something wrong?

You may want to think a bit further.
As -20 is less than 10, do you want that to show 2 decimals?
If what you intended was 2 decimals for numbers > -10 and < 10, you may want
[<=-10]-0;[<10]0.00; 0
--
David Biddulph

Format Cell/ Number/ Custom
[<10]0.00; 0

You talked about <10 and >10, but I guessed that you meant >=10.
--
David Biddulph

Dear experts,
I have a table which looks up values from a database; this table is
linked
to a cell where I can select what I want to see in the table.
Now, sometimes the values I want to see need to be in a 2 decimal format
(ex. 2.43) and sometimes they need to have no decimals at all (ex.
25000). It
really all depends on their magnitude - when <10 then I need to have 2
decimals, if > 10 then no decimal is needed.
Is there a way to do this in Excel 2003?
Many thanks for your help!
Kind regards