excel 2003 zero as last decimal

  • Thread starter Thread starter Jarle
  • Start date Start date
J

Jarle

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.
 
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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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!*


:

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

:

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!*


:

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