# Need a Formula

#### Swagg

##### New Member
I need a formula where 0 X 0 = 100%

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Anthony47

##### Well-known Member
I think you mean "0 / 0" = 100%
If the portion is in A2 and the total is in B2, you normally use =A2/B2 , but in case that the total is 0 you will receive a #DIV/0! error.
To get what you need, you may use
Code:
=IF(B2=0,1,A2/B2)

Bye,

#### Swagg

##### New Member
I can't seem to get it to work. Should the calculation be performed first then based on the result the if statement does it's thing.

Maybe I need to chnage my question,

A1 = Due
B1 = Complete

C1 (b1/c1) (this is what I have currently, however when I have 0 due and 0 completed I get the #DIV/0!.

Can I have a formula built into C1 that will convert the #DIV/0! result to 100? I really don't want to do this in another cell.

#### mortgageman

##### Well-known Member
1) I would change Anthony's suggestion to =If(sum(a2,b2)=0,1,a2/b2)
since you want the answer to be 1 ONLY when both a2 and b2 are equal to zero. If b2=0 and a2=5, you would want ??????? (Maybe #NA ??)

2) More importantly, and this is a common refrain from me and some others on this board, you should find out if there is any underlying issue that is causing b2 to be equal to zero. It is not clear to me, LH's rule aside, that you should want to "mask" divide by zero problems. It is like pain - usually it is telling you something that you should want to know.

#### Swagg

##### New Member

0 means nothing was due or completed for that task

#### Swagg

##### New Member
1) I would change Anthony's suggestion to =If(sum(a2,b2)=0,1,a2/b2)
since you want the answer to be 1 ONLY when both a2 and b2 are equal to zero. If b2=0 and a2=5, you would want ??????? (Maybe #NA ??)

I want the calculation to be done a2/b2.

I have modified the formula with your suggestion and it is now giving me the 100% when I have 0 due and 0 completed, how ever it is not calculating when I have numbers other than 0. I'm getting 0%.

#### mortgageman

##### Well-known Member

If you have in cell c1 the formula b1/c1, you should be getting a "circ". Was that a typo?

Why do you want 100% for nothing due and nothing completed?

#### mortgageman

##### Well-known Member
One last thing, and this shouldn't really matter, but on the very small chance that you have a negative due and a negative completed and they are both the same value - my formula would breakdown. I suspect that you would have a greater chance of slipping in a dry shower.

#### Swagg

##### New Member
Why do you want 100% for nothing due and nothing completed?[/quote]

Each category receives points based on the calculated %, 0% - 25% = 25points, 26% - 75% = 50 points and >76% = 100 points, these points are then summed and an overall Index score is awarded.
It was decided that not giving points for 0's was not fair across the board, in some categories 0 are good (Regulatory findings).

So, I want to automate my spreadsheet to perfom all the calculations and I will aslo use the conditional formatting to also color code based on calculation.

I have to keep this spreadsheet for five sites.

#### Swagg

##### New Member
Mortgageman,

Thank you, I have it working now.

Replies
3
Views
221
Replies
2
Views
56
Replies
5
Views
434
Replies
7
Views
103
Replies
1
Views
176

1,141,626
Messages
5,707,491
Members
421,511
Latest member
mgroah1

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