# Formula to Overcome #DIV/0!

#### szbierski

##### New Member
I require a formula that needs to solve a couple of different calculations and this is the starting point of the formula I have used:

=IF(A1=0,0,B1/A1)

What I would like it to do is that if B1 & A1 are both 0, then answer should be 0. If A1 has a value that is not 0, then use B1/A1. This can be resolved by my current formula

The next bit is the tricky bit. If A1 is 0 and B1 is greater than zero, the answer is 1, if B1 is less than 0, the answer is -1.

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### mikerickson

##### MrExcel MVP
I think this will do what you want

=IF(A1=0, SIGN(B1), B1/A1)

#### szbierski

##### New Member
Thank you very much. It has worked a treat!

#### John Davis

##### Well-known Member
I think this will do what you want

=IF(A1=0, SIGN(B1), B1/A1)

Mike: Sorry for the intrusion. What is SIGN and what does it mean. I'm not mathematically inclined.

#### Diffy

##### Well-known Member
Sign refers to either positive or negative values
A positive number like 8 can also be represented as +8 with a "+" sign indicating that the 8 is indeed positive.
A negative number like -9 is usually written with a "-" sign indicating that the 9 is indeed negative.

I hope this helps.

#### John Davis

##### Well-known Member
Sign refers to either positive or negative values
A positive number like 8 can also be represented as +8 with a "+" sign indicating that the 8 is indeed positive.
A negative number like -9 is usually written with a "-" sign indicating that the 9 is indeed negative.

I hope this helps.

Thanks for the reply and explanation. After rummaging through this message board for a couple of years. That's the first time I seen it used in a formula.

#### Scott Huish

##### MrExcel MVP
From the help file:

SIGN function

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

#### John Davis

##### Well-known Member
From the help file:

SIGN function

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and -1 if the number is negative.

Thanks. You're right. It's in the Insert Function List too. Sometimes the most obvious place to look is last place you look. It through me off because I just had never seen it used before.

#### mikerickson

##### MrExcel MVP
Insert Function is my favorite library of Excel functions.

Replies
5
Views
1K
Replies
5
Views
237
Replies
1
Views
498
Replies
1
Views
333
Replies
1
Views
238

1,191,628
Messages
5,987,768
Members
440,109
Latest member
mitra2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back