IF and percentage calculation

[Executioner]

Hi everyone,
I'm using Excel 2003, and I'm performing a simple calculation with the end result as a percentage. The issue is I need to have the results subtracted from the initial calculation.

Example:
CELL F4: # items = 133
CELL I4: # items compliant = 125

My initial formula is: =IF(F4-I4=0,"100%",(F4-I4)/F4) which gives me 18.8% (formatted as a %) which is correct, but I need that subtracted that value from 100% so my result will be 81.2%. I can do it by adding another column and hiding it, but I'm hoping it can be done with one formula.

Not sure how do get the formula created to get that result.

 

[tc88]

Hi everyone,
I'm using Excel 2003, and I'm performing a simple calculation with the end result as a percentage. The issue is I need to have the results subtracted from the initial calculation.

Example:
CELL F4: # items = 133
CELL I4: # items compliant = 125

My initial formula is: =IF(F4-I4=0,"100%",(F4-I4)/F4) which gives me 18.8% (formatted as a %) which is correct, but I need that subtracted that value from 100% so my result will be 81.2%. I can do it by adding another column and hiding it, but I'm hoping it can be done with one formula.

Not sure how do get the formula created to get that result.

=IF(F4-I4=0,"100%",(1-(F4-I4)/F4)

Except my result is 94%, 125/133=93.9849624%

 

[Weaver]

=if(f4-i4=0,1,1-(f4-i4)/f4)

 

[Joe4]

I think you are making this a lot harder than it has to be!
It sounds like you just want:

=I4/F4

No IF necessary...
Is that right or am I not understanding your question?

 

[Executioner]

I think you are making this a lot harder than it has to be!
It sounds like you just want:

=I4/F4

No IF necessary...
Is that right or am I not understanding your question?

I have the IF statement there so when a calculation is 143-143=0, it will display 100%, but using your simple example is correct. Thanks for that. I guess I was making it harder than it should be.

 

[Weaver]

so

=if(f4-i4=0,1,i4/f4)

 

[Joe4]

have the IF statement there so when a calculation is 143-143=0, it will display 100%, but using your simple example is correct. Thanks for that. I guess I was making it harder than it should be.

But you are saying that you have 143 in cell F4 and also in cell I4, right?

Then
=I4/F4
=143/143
=1
=100% (if formatted as a percent)

So my formula works, and the IF is unnecessary.

 

[Joe4]

=if(f4-i4=0,1,i4/f4)

Also unnecessary and says the same thing as the formula I posted.

If F4-I4=0, then F4=I4.
If F4=I4, then I4/F4=1.
So the IF statement is redundant and completely unnecessary.

So this
=if(f4-i4=0,1,i4/f4)
can be simplified to this:
=I4/F4