#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

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.

milesUK

Active Member
Joined
Jan 5, 2003
Messages
388
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
Joined
Jun 8, 2010
Messages
769
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
Joined
Jan 5, 2003
Messages
388

ADVERTISEMENT

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
Joined
Jun 8, 2010
Messages
769
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
Joined
Jan 5, 2003
Messages
388

ADVERTISEMENT

In that case try:

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

ApolloID

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
Joined
Dec 2, 2002
Messages
1,940
Office Version
  1. 2019
Platform
  1. Windows
Or this….

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

Regards
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top