Format as number or accounting with if statement

[finguy]

I would like to format a cell with a number being divided by 1,000 as either "number" or "accounting" depending on if the result is actually "0" or less than 1,000 (where it would display "0" instead of "-")

 

[UniMord]

Welcome aboard the Board!

If I understand your question correctly, you want a Custom Format.
Select the range that you want to format, hit Ctrl <CTRL><CTRL>+ 1 to bring up the Format Cells dialog box. The first tab is Number. In the Category pane, select Custom. In the Type box on the right, enter:
[>=1000]#,###;[=0]"-";"0"
Hit OK and you're done.

This will give you a comma delimited number (no decimals) if equal to or greater than 1,000, a dash if it's really zero, and a zero if it's less than 1,000.

Search for Custom Format in Excel Help to learn all about creating Custom Formats. You'll be happy you did.

 

[finguy]

Thanks. That works fine, unfortunately I'm also rounding the number to 0 decimal places, which actually makes the answer 0 whenever the number I'm dividing by 1,000 is less than 1,000. For financial reporting, even though in this case the "-" and the 0 are essentially the same, they want to see a "0" whenever there was a value, which in this case was less than a million (the smallest number we're reporting).

I think I need some kind of "if" statement to make this work.

 

[finguy]

to clarify, what if the number that I am testing with the "if" statement in the format is in a different cell?

 

[UniMord]

If I understand what it is that you need, you don't need to do the rounding at all, Custom Formatting happily handles that too, at no extra charge.

Use: [>=1000]#,###,;[=0]"-";"0"
(It's identical to the previous one, except for the addition of a comma)

This will lop the final 3 zeros off the number as well. All values below 1,000 will still be showing as zeroes, because the value is still there. Remember, the real, live numbers remain unchanged, only the formatting is different.

Does this help?

 

[finguy]

We're getting closer: this example may help:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl220 height=16 width=64>A</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl220 width=64>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl221 height=17> $ 201 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl218> $ 200,548 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl217 height=17> 24 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl219> 23,564 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl217 height=17> 1 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl219> 500 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl217 height=17> 0 </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl219> 255 </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl217 height=17> 0</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl219> 65 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 12.75pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl217 height=17> - </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl214 align=right>0</TD></TR><TR style="HEIGHT: 24.95pt; mso-height-source: userset" height=33><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: white; HEIGHT: 24.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl217 height=33> - </TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl214 align=right>0</TD></TR></TBODY></TABLE>

I'm taking the input in column "B" and dividing by 1,000 and rounding to 0 places (for totals footing purposes) in column "A". So actually, any number in "B" that's less than 500 will be 0 in "A". The challenge is that any values in "B" that are not 0 but less than 500, I want to display as 0, and any actual 0's in B should display as a "-" in "A". So, essentially its a conditional format in "A", "number" or "accounting" based on column "B"'s value.

 

[UniMord]

If that's all you want, then it's a piece of cake!
In column A the formula will be =B1/1000 (copy down).
Go back to the Format Cells dialog box and set the format to Accounting, choose no decimals, and you're done!

The fractions will appear as zeroes, and the zeroes will appear as dashes.

 

[Scott R]

If that's all you want, then it's a piece of cake!
In column A the formula will be =B1/1000 (copy down).
Go back to the Format Cells dialog box and set the format to Accounting, choose no decimals, and you're done!

The fractions will appear as zeroes, and the zeroes will appear as dashes.

This takes care of the individual cell entries, but won't handle the footing problem.
The sum formula will also need to round. Enter this array formula with Ctrl+Shift+Enter: =SUM(ROUND(A1:A8,0))
In the example, I get a total of 226, even though the underlying total is 224,932.

 

[finguy]

thanks to both of you; Unimord's suggestion with Scott R's adjustment for the summing works fine, although when using accounting you always get a dollar sign, which I don't need in each line, so my guess is I would still need a custom format?

 

[UniMord]

There is no need for a Custom Format. In the Format Cells dialog box, when you choose Accounting, there is a Symbol dropdown on the right; choose None.