changing decimal places according to formula result

  • Thread starter Thread starter Valeria
  • Start date Start date
V

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
 
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
 
Format Cell/ Number/ Custom
[<10]0.00; 0

You talked about <10 and >10, but I guessed that you meant >=10.
 
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...
 
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

David Biddulph said:
Format Cell/ Number/ Custom
[<10]0.00; 0

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

Valeria said:
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
Click to expand...
Click to expand...
 
This works really great and is really easy to do!!! Many thanks.
Kind regards
--
Valeria


David Biddulph said:
Format Cell/ Number/ Custom
[<10]0.00; 0

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

Valeria said:
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
Click to expand...
Click to expand...
 
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?


David Biddulph said:
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

David Biddulph said:
Format Cell/ Number/ Custom
[<10]0.00; 0

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

Valeria said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top