Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
Suppose your number is in A1. Try this in B1:
=TEXT(A1,IF(A110,"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(A110,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 On Sep 11, 10:29*am, Valeria wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
Format Cell/ Number/ Custom
[<10]0.00; 0 You talked about <10 and 10, but I guessed that you meant =10. -- David Biddulph "Valeria" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
This works really great and is really easy to do!!! Many thanks.
Kind regards -- Valeria "David Biddulph" wrote: Format Cell/ Number/ Custom [<10]0.00; 0 You talked about <10 and 10, but I guessed that you meant =10. -- David Biddulph "Valeria" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
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" <groups [at] biddulph.org.uk wrote in message ... Format Cell/ Number/ Custom [<10]0.00; 0 You talked about <10 and 10, but I guessed that you meant =10. -- David Biddulph "Valeria" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
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" wrote: 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" <groups [at] biddulph.org.uk wrote in message ... Format Cell/ Number/ Custom [<10]0.00; 0 You talked about <10 and 10, but I guessed that you meant =10. -- David Biddulph "Valeria" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
changing decimal places according to formula result
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... -------------------- (MS-Exl-Learner) -------------------- "Valeria" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
Formula for: Format Decimal places? | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
decimal places within a formula | Excel Discussion (Misc queries) | |||
Changing default decimal places | Excel Discussion (Misc queries) |