excel 2003 zero as last decimal

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.
 
L

Luke M

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.
 
J

Jarle

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.
 
F

Fred Smith

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.
 
J

Jarle

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.
 
F

Fred Smith

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top