Need a Formula

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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,
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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%.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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