Xcel formula question

[fistofrage]

I need a formula that is going to make any cell with 0(zero) in it and a Div/# error to read as a "-". Should be a common if statement, but can't figure out what to do with the error cells.

Can someone point me in that direction.

Thanks.

[Blzer]

Re: Xcel formula question

I'm afraid I don't have an answer, but I actually wanted the exact same thing. I had stats over the summer for my sister's softball team, and people with ratios that didn't include a nonzero number stayed as #DIV/0!. When I tried to convert it to a hyphen myself, it screwed up the [stat] column totals and averages.

I couldn't find anything under the Format Cells option, either. I'll be looking around some more until we can hopefully find an answer.

[p_rushing]

Re: Xcel formula question

=IF(B1>0,A1/B1,"-")

A1=10
B1=0

C1='-'

A2=10
B2=20

C2=.5

[LetsGoBucs]

Re: Xcel formula question

In your cell, use this formula:

=IF(OR(ISERROR(Formula),0),"-",Formula)

This will put a dash in the cell if there is an error or a 0, otherwise it will use the formula result.

[LetsGoBucs]

Re: Xcel formula question

=IF(B1>0,A1/B1,"-")

A1=10
B1=0

C1='-'

A2=10
B2=20

C2=.5

This will only take care of the situation where the result is a zero, not if there is an error (#DIV/0!)
- Nevermind, rethought the equation and it will not do the division if it will cause an error

[fistofrage]

Re: Xcel formula question

=IF(B1>0,A1/B1,"-")

A1=10
B1=0

C1='-'

A2=10
B2=20

C2=.5

There are some situations where the number could be negative as well, can I put an or statement in there so B1>0 OR B1<0,A1/B1,"-" ?

[p_rushing]

Re: Xcel formula question

There are some situations where the number could be negative as well, can I put an or statement in there so B1>0 OR B1<0,A1/B1,"-" ?

For that I would just do B1<>0, B1 does not equal 0

[fistofrage]

Re: Xcel formula question

In your cell, use this formula:

=IF(OR(ISERROR(Formula),0),"-",Formula)

This will put a dash in the cell if there is an error or a 0, otherwise it will use the formula result.

This wroks great. Thanks.