# #DIV/0! error

#### ApolloID

##### Well-known Member
Hi, i have this three situations:

A..................B.................C
355,626 54,882 -85%
0 20,662 #DIV/0!
-111,43 0 100%

In C i have this formula: =(B10-A10)/ABS(A10)
This formula calculates the diference between B1 and A1 as percentage.

I need a formula to give me the correct result in all three situations.
The first situation is correct. I need the formula for the last too.

Can this be done?
Thanks!

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### xld

##### Banned
Try

=IF(OR(A10=0,B10=0),0,(B10-A10)/ABS(A10))

#### milesUK

##### Active Member
ApolloID,

Depends on what answers you want. Initially I would say that the two answers would be Infinity & 100% respectively. but would you want the second to retirn zero, blank, error message, etc?

#### ApolloID

##### Well-known Member
The problem is that i have this situation:
A...................B..................C
35,007.......278,128...........694%

The XLD formula looks good, but if the above situation gives me 694%, then i need the formula to give me the exact result:
A..................B.................C
355,626.....54,882...........-85%
0..............20,662.........20662%
-111,43........0.................0%

I think in third situation 0% is the most correct.
The same for :
A............B
0............0

Thanks!

#### milesUK

##### Active Member

Do you want the % to be based on the larger of the two values?

3 10 70%
10 3 70%

3 0 100%
0 3 100%

#### ApolloID

##### Well-known Member
I need the result to be with "-" (minus)
and yes, to calculate the procentage by the larger value.
3 10 70%
10 3 -70%
Thanks!

#### milesUK

##### Active Member

In that case try:

=(B1-A1)/MAX(A1:B1) and format as '%'.

#### ApolloID

##### Well-known Member
It,s working good, but i have inserted some columns with datas between A1 and B1 so the formula gives me an error now. I have 8 columns between.
So now is :
A.......J......K
I think the problem is this part of your formula (A1:B1)

##### MrExcel MVP
It,s working good, but i have inserted some columns with datas between A1 and B1 so the formula gives me an error now. I have 8 columns between.
So now is :
A.......J......K
I think the problem is this part of your formula (A1:B1)

...which you can correct...

=(B1-A1)/MAX(A1,B1)

#### bosco_yip

##### Well-known Member
Or this….

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

Regards

Replies
3
Views
614
Replies
4
Views
231
Replies
1
Views
219
Replies
3
Views
453
Replies
9
Views
330

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,681
Messages
5,833,087
Members
430,190
Latest member
beaviss

### 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