Help in understanding this formula

[Divablair]

Wondering if you could tell me something about this formulabeing used.


I understand what the formula is doing and how it worksbut what I don’t know is “why”? Why are they using-1? What does that even mean? When you usethe -1 you get a total different result so I’m trying to understand why they aredoing this. They talk about it being percentiles but how do you get 100% if its 0/5 for the month would normally be 0%.



<u5></u5>
EXAMPLE: This the formula being used at Corporate. I get a result of 100% when using this formula withthe -1




=IF(B7>D7,0,IF(B8=0,"N/A",(IF(((B7/D7)-1)=(1),(100),(ABS((B7/D7)-1))*100))))



Total National Goal Monthly Score

(B7) Numerator 0 (D7) 8.8 100 (answer)


(B8) Denominator 5

<u5></u5>

<u5></u5>
I hope this makes sense in an email.

Thanks.

 

[steve the fish]

Its creating an inverse percentage. B7/D7 always produces a fraction of 1 because B7 is always less than D7.

 

[shg]

I don't know what it's calculating, but it could certainly be simplified:

=IF(B7>D7, 0, IF(B8=0, "N/A", IF(B7/D7=2, 1, 1-B7/D7)))

Format as %.

 

[Divablair]

But why do they do that? Wouldn't my real number be 0% instead of 100%. How can I be 100% if I missed zero opportunities. I'm sorry my example got messed up when it up loading. I'm having issues in typing on this form for some reason.

 

[Marcelo Branco]

I don't understand what is the role of B8 in the formula. You said it's the Denominator, but i can't see it being used in the calculations (???)

Could you provide some examples along with the desired results?

M.

 

[Divablair]

If the denominator happens to be 0 (meaning for the month there might have been zero cases done on a specific measure, than the monthly Score would be N/A.) so the rest of the formula does not matter.

 

[Divablair]

Its creating an inverse percentage. B7/D7 always produces a fraction of 1 because B7 is always less than D7.

Thanks. I've never heard of inverse percentage so I will read up on this.

 

[Divablair]

OK, this helps ALOT about the inverse percentage and why its opposite. Thanks everyone.