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

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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,192
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,938
Office Version
2019
Platform
Windows
Or this….

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

Regards
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,888
Messages
5,513,996
Members
408,980
Latest member
Naomi_

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top