Need a Formula

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
Joined
Mar 29, 2006
Messages
2,238
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
Joined
Dec 8, 2005
Messages
38
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
Joined
Jun 30, 2005
Messages
2,015
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
Joined
Dec 8, 2005
Messages
38

ADVERTISEMENT

0 means nothing was due or completed for that task
 

Swagg

New Member
Joined
Dec 8, 2005
Messages
38
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
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

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
Joined
Jun 30, 2005
Messages
2,015
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
Joined
Dec 8, 2005
Messages
38
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.
 

Forum statistics

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