Formula Problem

[dbrooks76]

Hi,

I'm trying to get a spreadsheet to read a formula that will return a dash (-) as a zero (0) whilst any other number, including zero (0), is to be returned as a one (1).

My current formula reads a dash and zero both as zero's and so returns the wrong answer.

=IF(H16="-",1,0)
if true = 1
if false = 0

Any idea's how I can prevent this?

Cheers,

Dave

 

[Peter_SSs]

dbrooks76

Welcome to the Mr Excel board!

I may not have understood correctly, but I think you just need to swap the 0 and 1 in your formula. Try this:
=IF(H16="-",0,1)

If this is not it please give a few examples of data and expected results.

 

[dbrooks76]

Tried That!

Hi,
thanks for quick reply. But I've tried that and it throws the zero figures back as a zero.
I'm after any number to be returned as a one (1) and any dashes (-) to be zero.

So if:
H16 = -
H17 = 2.0
H18 = 2.3
H19 = 0
H20 = 1.2
H21 = -

The answers should be:
H16 = 0
H17 = 1
H18 = 1
H19 = 1
H20 = 1
H21 = 0

???

 

[Peter_SSs]

dbrooks76

Your last post lists the answers in the same cells as the original data. I assume that was a mistake and the results are to be in an adjacent cell. If not post back.

Here is my sheet with the formula in I16 (copied down): =IF(H16="-",0,1)

Mr Excel.xls
	G	H	I	J
15
16		-	0
17		2	1
18		2.3	1
19		0	1
20		1.2	1
21		-	0
22
Sheet1

 

[Richard Schollar]

Or maybe:

=ISNUMBER(H16)+0


Richard

 

[patrickmuldoon99]

Is it a dash that is being displayed, or is it a zero formatted as accounting with no currency?

 

[dbrooks76]

The dash is where no value is known. and the zero's are actual values (where present).

Strangely just tried the formula on a new spreadsheet and it works, but the company spreadsheet doesn't!! Weird!! I think i'm stepping into bigger problems...!!!

Thanks for all your help though guys!

 

[Peter_SSs]

Strangely just tried the formula on a new spreadsheet and it works, but the company spreadsheet doesn't!! Weird!!

On the company spreadsheet, select H16 and then go Format|Cells...|Number What category is the cell formatted as?
Do the same on cell I16 (or wherever the formula is located).

 

[dbrooks76]

Both the value cell and formula cell are categorised as "general".

Do I need change this?

 

[Richard Schollar]

You shouldn't need to change that - General is fine