#DIV/0! error

ApolloID

Well-known Member
Joined
Jun 8, 2010
Messages
769
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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?
 
Upvote 0
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!
 
Upvote 0
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%
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top