Formatting or Formula??

[stumped73]

Hi all, hopefully this is a simple problem that I'm having but I'm stumped.

I'm using the formula =IF(ISERROR(C5*K5),"",C5*K5) so that if the cell is #N/A it will display nothing at in the cell. I've used a similar formula in numerous columns and it's works great but in a few columns it is not working and shows $ - instead. I've checked for conditional formatting and there is none. I changed the formula to multiply different columns and it actually works. It seems to be something with the formula, but it calculates properly.

Any thoughts?
Stumped73

 

[Sektor]

I think you see "####". Just make this column wider.

 

[GlennUK]

What do you expect to see in the cell if the result is zero?

 

[stumped73]

Thanks for the quick response.

If the result is 0 I don't want to see anything at all. It is showing $- as formatted for accounting. It's not column width and it doesn't show ###.

 

[Sektor]

Not 2 guess, post your workbook please.

 

[GlennUK]

Thanks for the quick response.

If the result is 0 I don't want to see anything at all. It is showing $- as formatted for accounting. It's not column width and it doesn't show ###.

... and what number format have you applied to the cell? Accounting, or a custom one? If not a custom one, why not?

A custom one that is accounting but blank for zeroes:

_-£* #,##0.00_-;-£* #,##0.00_-;;_-@_-

 

[stumped73]

It's formatted for accounting. I tried custom as well same thing. Accounting does work for the other columns.

I'm using 2003 but get the same result when I opened the file in 2007.

Unfortunately I'm not able to post an attachment as per the posting rules. Maybe since I'm new here.


EDIT
I tend to think it's more a formula issue. For example, I just modified the formula to =IF(ISERROR(C5*K5),"XYZ",C5*K5) I added "XYZ" in place of the "" just to see if it would show XYZ and it still shows $ -.

 

[GlennUK]

In that case it's probable a number close to zero, but not zero. Change your formula to be:

=IF(ISERROR(C5*K5),"",ROUND(C5*K5,2))

 

[stumped73]

I've posted a screenshot to a personal web space. Not as good as the actual worksheet but it's the best I can do right now.

http://users.eastlink.ca/~lauriannriley/

The round formula doesn't help as I want the cells blank where the is nothing being multiplied. Hopefully the screenshot will help clarify.

 

[GlennUK]

This is using normal Accounting format:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
1
2
3
4	Est Qty								Price	Total Sales
5	5								4.00	20.00
6	10								5.00	50.00
7	15								6.00	90.00
8										-
9										-
10										-
11										-
12										-
Sheet40

... and this is using my custom number format:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
4	Est Qty								Price	Total Sales
5	5								4.00	20.00
6	10								5.00	50.00
7	15								6.00	90.00
8
9
10
11
12
13
14
15
16
17
Sheet40