Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 zero as last decimal
Hi.
I need to print out different number of decimals depending of cell value. If the value is <= 0.30 it should print out 2 decimals. If the value is greater it should have 1 decimal. This works by if/round formula. But if the last decimal is zero excel ignores it. How can I make this work? I tried to write a custom setting for cells, but then I got 0.00 in my empty cells. Custom setting [0,3]#0,0;[<=0,3]#0,00 (I use Norwegian decimal point ie ,) excel then slaps on ;Standard at the end of the setting. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 zero as last decimal
Just need to tell XL what do to with blanks.
[0,3]0,0;[<=0,3]0,00;; Note that I created this using normal deicmals, and I am assuming you can just replace the periods with commas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jarle" wrote: Hi. I need to print out different number of decimals depending of cell value. If the value is <= 0.30 it should print out 2 decimals. If the value is greater it should have 1 decimal. This works by if/round formula. But if the last decimal is zero excel ignores it. How can I make this work? I tried to write a custom setting for cells, but then I got 0.00 in my empty cells. Custom setting [0,3]#0,0;[<=0,3]#0,00 (I use Norwegian decimal point ie ,) excel then slaps on ;Standard at the end of the setting. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 zero as last decimal
I'm still getting 0,00 in cells that shoul have been blank. In addition my <
signs disappear. If there's anyway to put out a workbook it would be easier to explain. Mine is full of formulas, I know that macros and programming could remove some. But I'm not familiar with programming yet. Best regards Jarle "Luke M" wrote: Just need to tell XL what do to with blanks. [0,3]0,0;[<=0,3]0,00;; Note that I created this using normal deicmals, and I am assuming you can just replace the periods with commas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jarle" wrote: Hi. I need to print out different number of decimals depending of cell value. If the value is <= 0.30 it should print out 2 decimals. If the value is greater it should have 1 decimal. This works by if/round formula. But if the last decimal is zero excel ignores it. How can I make this work? I tried to write a custom setting for cells, but then I got 0.00 in my empty cells. Custom setting [0,3]#0,0;[<=0,3]#0,00 (I use Norwegian decimal point ie ,) excel then slaps on ;Standard at the end of the setting. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 zero as last decimal
It probably means that your value is not exactly zero. It could be, say,
0.00001 which displays as 0.00. Try rounding your result to 2 decimal places (=round(yourformula,2)). Regards, Fred. "Jarle" wrote in message ... I'm still getting 0,00 in cells that shoul have been blank. In addition my < signs disappear. If there's anyway to put out a workbook it would be easier to explain. Mine is full of formulas, I know that macros and programming could remove some. But I'm not familiar with programming yet. Best regards Jarle "Luke M" wrote: Just need to tell XL what do to with blanks. [0,3]0,0;[<=0,3]0,00;; Note that I created this using normal deicmals, and I am assuming you can just replace the periods with commas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jarle" wrote: Hi. I need to print out different number of decimals depending of cell value. If the value is <= 0.30 it should print out 2 decimals. If the value is greater it should have 1 decimal. This works by if/round formula. But if the last decimal is zero excel ignores it. How can I make this work? I tried to write a custom setting for cells, but then I got 0.00 in my empty cells. Custom setting [0,3]#0,0;[<=0,3]#0,00 (I use Norwegian decimal point ie ,) excel then slaps on ;Standard at the end of the setting. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 zero as last decimal
OK, here's my formula:
=IF(K8="<";"<";IF(K8<0,3;IF(K31<-0,05;ROUNDUP(K8;2);ROUND(K8;2));IF(K31<-0,05;ROUNDUP(K8;1);ROUND(K8;1)))) If I try to round this I get an error. Regards Jarle "Fred Smith" wrote: It probably means that your value is not exactly zero. It could be, say, 0.00001 which displays as 0.00. Try rounding your result to 2 decimal places (=round(yourformula,2)). Regards, Fred. "Jarle" wrote in message ... I'm still getting 0,00 in cells that shoul have been blank. In addition my < signs disappear. If there's anyway to put out a workbook it would be easier to explain. Mine is full of formulas, I know that macros and programming could remove some. But I'm not familiar with programming yet. Best regards Jarle "Luke M" wrote: Just need to tell XL what do to with blanks. [0,3]0,0;[<=0,3]0,00;; Note that I created this using normal deicmals, and I am assuming you can just replace the periods with commas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jarle" wrote: Hi. I need to print out different number of decimals depending of cell value. If the value is <= 0.30 it should print out 2 decimals. If the value is greater it should have 1 decimal. This works by if/round formula. But if the last decimal is zero excel ignores it. How can I make this work? I tried to write a custom setting for cells, but then I got 0.00 in my empty cells. Custom setting [0,3]#0,0;[<=0,3]#0,00 (I use Norwegian decimal point ie ,) excel then slaps on ;Standard at the end of the setting. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel 2003 zero as last decimal
Showing your formula helps a lot. It would have saved you a lot of time if
you had posted it at the beginning. With this formula, rounding is not the problem, as you're rounding every result. You can get your "<" back by telling Excel what to do with text, as in: [0,3]0,0;[<=0,3]0,00;;@ However, as zero is less than 0,3, it's covered by the 0,00 format, so can't be treated any differently. To get a blank for zero, you will need to check for it in your formula. Something like: =IF(K8="<";"<";IF(K8=0;" ";IF(K8<0,3;IF(K31<-0,05;ROUNDUP(K8;2);ROUND(K8;2));IF(K31<-0,05;ROUNDUP(K8;1);ROUND(K8;1))))) Regards, Fred "Jarle" wrote in message ... OK, here's my formula: =IF(K8="<";"<";IF(K8<0,3;IF(K31<-0,05;ROUNDUP(K8;2);ROUND(K8;2));IF(K31<-0,05;ROUNDUP(K8;1);ROUND(K8;1)))) If I try to round this I get an error. Regards Jarle "Fred Smith" wrote: It probably means that your value is not exactly zero. It could be, say, 0.00001 which displays as 0.00. Try rounding your result to 2 decimal places (=round(yourformula,2)). Regards, Fred. "Jarle" wrote in message ... I'm still getting 0,00 in cells that shoul have been blank. In addition my < signs disappear. If there's anyway to put out a workbook it would be easier to explain. Mine is full of formulas, I know that macros and programming could remove some. But I'm not familiar with programming yet. Best regards Jarle "Luke M" wrote: Just need to tell XL what do to with blanks. [0,3]0,0;[<=0,3]0,00;; Note that I created this using normal deicmals, and I am assuming you can just replace the periods with commas. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jarle" wrote: Hi. I need to print out different number of decimals depending of cell value. If the value is <= 0.30 it should print out 2 decimals. If the value is greater it should have 1 decimal. This works by if/round formula. But if the last decimal is zero excel ignores it. How can I make this work? I tried to write a custom setting for cells, but then I got 0.00 in my empty cells. Custom setting [0,3]#0,0;[<=0,3]#0,00 (I use Norwegian decimal point ie ,) excel then slaps on ;Standard at the end of the setting. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Point Vertical Alignment in Excel 2003 | New Users to Excel | |||
Decimal point will not hold 2 places in Excel 2003 | Excel Worksheet Functions | |||
Decimal Point Excel 2003 | Excel Discussion (Misc queries) | |||
How do I override fixed decimal place settings in EXcel 2003? | Excel Worksheet Functions | |||
Decimal places in Excel 2003 | Excel Worksheet Functions |