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]

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

Do I need change this?

No, don't change it. On the company sheet:
Are your formula in I16 and below? If not where?
What results does the company sheet give for the sample data used in my post?

 

[dbrooks76]

Hi,

The formula is in the column adjacent to the H-cell value (i.e. in Col I).
The formaula returns the following (I'd post a image of it but our system prevents this):

H - Col

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


I-Col

I16 = 1
I17 = 1
I18 = 1
I19 = 0
I20 = 1
I21 = 1

I'm after anything other than a dash (-) to be a one (1) and the dashes (-) to be zero's.

 

[Richard Schollar]

Hi

Did you give mine a try? Shouldn't really work any different to Peter's though.

=ISNUMBER(H16)+0

 

[Peter_SSs]

dbrooks76

Can you confirm (that is post to the board again) the formulas you have in I16 (next to the dash) and I19 (next to the 0)?

In an empty cell put =LEN(H16) What result does it give?

 

[dbrooks76]

The formula is the same next to either the dash or the zero, which is:

=IF(H16="-",1,0)

Swapping the 1 or 0 (i.e., =IF(H16="-",0,1) simply reverses the result, it doesn't prevent the spreadsheet from reading a dash and zero as the same thing (on the company spreadsheet that is).
A blank spreadsheet shows, as you posted, the correct results - so they're must be some option selection wrong on this spreadsheet.

The =LEN(H16) formula returns one (1).

Very confusing!!

 

[Peter_SSs]

The formula is the same next to either the dash or the zero, which is:

=IF(H16="-",1,0)

Well, the 1 and the 0 definitely should be the other way round if you want a "-" to return a 0. And I hope you really mean that cell I19 formula starts with =IF(H19 not =IF(H16

I can't figure what else could be causing the results you are getting on that company sheet.

 

[dbrooks76]

Sorry, yes that should have been H19.

I think I'll just produce a new spreadsheet - sounds much simpler.

Thanks for your help thought! Much appreciated!!!