Percentage of target formula

**AndrewSE** · 03-08-2011, 12:21 PM

Hello all,

New to the forums.

I'm in the process of creating a new report and I'm stuck.

My report consists of a set target in a field, a score and percentage of target.

e.g Target is 400

score is 450.

Percentage is 1.125 of target. (=score/target)

Now what I would like to do is create a formula that does not go above 100% , but instead work in reverse if you go over the target.

E.g. Target 400 .. Score was 450 ... percentage of target is 87.5%

Thanks in advanced.

**ChemistB** · 03-08-2011, 12:29 PM

=IF(score>target, target/score, score/target) Is that what you are looking for?
Note: if target is 400 and score is 450, score/target is 88.9%, not 87.5%

**MarvinP** · 03-08-2011, 12:37 PM

Hi AndrewSE and welcome to the forum
If A1 = 400 and B1 = 450 then in C1

Please Login or Register  to view this content.

should do it for you.

**DonkeyOte** · 03-08-2011, 12:42 PM

A little late to the party but perhaps also either of:

Please Login or Register  to view this content.

(A1 being Target)

though the above assumes B1 is not negative

**ConneXionLost** · 03-08-2011, 12:51 PM

If you don't want a negative result (when the score gets larger than twice the target), then:

Please Login or Register  to view this content.

**AndrewSE** · 03-08-2011, 12:57 PM

Originally Posted by ChemistB

=IF(score>target, target/score, score/target) Is that what you are looking for?
Note: if target is 400 and score is 450, score/target is 88.9%, not 87.5%

That is the one I wanted!

Thank you all for the assistance !

This forum is great ! I will for sure learn a thing or two here ! Much appreciated !

**AndrewSE** · 03-08-2011, 01:33 PM

Ok one more question.

I have a group of sells with this formula representing each month of the year.
I have data for Jan / Feb... lets say 95% and 90%.. I have 0% for each remaining month.
95,90,0,0,0,0,0,0,0,0,0,0 Now when I use the normal average formula I get 15.4% since it is including all the fields. Now for the kicker. I still need it to factor in 0% if the percentage of targer is really 0% based on the field with the score.