Advanced number formatting of decimal points

[David]

I have a worksheet with multiple sumif functions whose
values are contantly changing.
I would like to show 1 decimal point if the absolute
value of the function is < 10 (ie. "9.8") however not
show a decimal if the absolute value is > 10 (ie. "-20").
I have tried using
=if(abs(sumif(xyz))<10,text(sumif(xyz),"#.#"),sumif(xyz))
however this has resulted in values < 1 showing up
as ".9" (where I need 0.9) and values that are actually a
whole integer (ie. 8.0) showing up as "8."
Additionally, even when the function does work and shows
something like a "3.9", it does not line up with the
numbers above and below it that don't have decimals.
Any help is greatly appreciated.
Thank you for your time!!
David

 

[J.E. McGimpsey]

one way:

Format/Cells/Number/Custom [<10]0.0;0_._0


Take a look at "About custom number formats" in XL Help.

 

[Ken Wright]

This seemed to work for me if used with the font Courier new:-

=IF(ABS(SUMIF($A$1:$A$30,"xyz"))>10,TEXT(SUMIF($A$1:$A$30,"xyz"),"#
"),TEXT(SUMIF($A$1:$A$30,"xyz"),"#.0"))

although a 0 looks pretty daft, in which case:-

=IF(SUMIF($A$1:$A$30,"xyz")=0,"",IF(ABS(SUMIF($A$1:$A$30,">0.4"))>10,TEXT(SUMIF(
$A$1:$A$30,">0.4"),"# "),TEXT(SUMIF($A$1:$A$30,">0.4"),"#.0")))

But they stop being numbers this way??

 

[Guest]

David,
Change "#.#" to "0.0", this will fix the .9 and 8. problems. I will look a little further into the lining up issue.

Good Luck,
Mark Graesser
(e-mail address removed)


----- David wrote: -----

I have a worksheet with multiple sumif functions whose
values are contantly changing.
I would like to show 1 decimal point if the absolute
value of the function is < 10 (ie. "9.8") however not
show a decimal if the absolute value is > 10 (ie. "-20").
I have tried using
=if(abs(sumif(xyz))<10,text(sumif(xyz),"#.#"),sumif(xyz))
however this has resulted in values < 1 showing up
as ".9" (where I need 0.9) and values that are actually a
whole integer (ie. 8.0) showing up as "8."
Additionally, even when the function does work and shows
something like a "3.9", it does not line up with the
numbers above and below it that don't have decimals.
Any help is greatly appreciated.
Thank you for your time!!
David