Simple (maybe) percentage forumla

[Luthier]

I have a forumla I want to calculate.

There are three cells that have a total percentage possible of 100% jus
adding them creates results different from what I'm wanting. Here is a
example:


F5 = -30%
F6 = 35%
F7 = -35%
F8 =sum(F5:F7) and it reads -30%

What I would like for it to read is -65% to show that out of a possibl
-100% to 100% the total was -65%

Does that make sense? If so how do I accomplish this

 

[plinius]

Il 13/10/2012 21:41, Luthier ha scritto:

I have a forumla I want to calculate.

There are three cells that have a total percentage possible of 100% just
adding them creates results different from what I'm wanting. Here is an
example:


F5 = -30%
F6 = 35%
F7 = -35%
F8 =sum(F5:F7) and it reads -30%

What I would like for it to read is -65% to show that out of a possible
-100% to 100% the total was -65%

Does that make sense? If so how do I accomplish this?

You need something like this, maybe...

=SUMIF(F5:F7,"<0")
=SUMIF(F5:F7,">0")

E.

 

[Luthier]

Il 13/10/2012 21:41, Luthier ha scritto:-

You need something like this, maybe...

=SUMIF(F5:F7,"<0")
=SUMIF(F5:F7,">0")

E.

Perhaps I'm not understanding or didn't make myself clear but I'm no
sure how an IF greater or less than 0 formula will achieve what I wa
seeking

 

[plinius]

Il 14/10/2012 06:14, Luthier ha scritto:

Perhaps I'm not understanding or didn't make myself clear but I'm not
sure how an IF greater or less than 0 formula will achieve what I was
seeking.

It is not clear what you seek.
The sum F5:F7 is really -30% but you want obtain -65%.
This is possible only adding negative values, otherwise I'm not
understanding your requirement.

E.

 

[Luthier]

Il 14/10/2012 06:14, Luthier ha scritto:-

It is not clear what you seek.
The sum F5:F7 is really -30% but you want obtain -65%.
This is possible only adding negative values, otherwise I'm not
understanding your requirement.

E.

Ok.. let me explain what the purpose of the sum is and that migh
explain it better.

This is going to be used as a evaluation tool. There are employees tha
are to be graded in three categories each having a weighted percentag
and when all are totaled there is a possible positive 100% or negativ
-100% and percentages in between. In my original scenario the employe
had two negative sections and one positive. The negative section
totaled -65% while the positive was 35% There is a total of 100
(granted negative and positive combined).

This will be used as a bonus tool. So in the above scenario th
employee lost 65% of the bonus due to negative areas, and retained 35
of the bonus. Had he/she scored perfectly then 100% of the potentia
bonus was earned. Had he/she scored poorly in all categories then 0% o
the bonus was earned.

Does that help clarify the purpose of it? In doing that hopefully tha
will shed some light on what I'm trying to achieve. I can easily do i
myself but if I can automate that with a formula then that would b
excellent. I appreciate the help and I thank you in advance

 

[plinius]

Il 14/10/2012 16:01, Luthier ha scritto:

Ok.. let me explain what the purpose of the sum is and that might
explain it better.

This is going to be used as a evaluation tool. There are employees that
are to be graded in three categories each having a weighted percentage
and when all are totaled there is a possible positive 100% or negative
-100% and percentages in between. In my original scenario the employee
had two negative sections and one positive. The negative sections
totaled -65% while the positive was 35% There is a total of 100%
(granted negative and positive combined).

This will be used as a bonus tool. So in the above scenario the
employee lost 65% of the bonus due to negative areas, and retained 35%
of the bonus. Had he/she scored perfectly then 100% of the potential
bonus was earned. Had he/she scored poorly in all categories then 0% of
the bonus was earned.

Does that help clarify the purpose of it? In doing that hopefully that
will shed some light on what I'm trying to achieve. I can easily do it
myself but if I can automate that with a formula then that would be
excellent. I appreciate the help and I thank you in advance.

Perhaps I'm undestanding.
Total = 100% the employee take whole bonus
Total = -100% the employee lose 100% of the potential bonus
total = 0% the employee lose 50% of the potential bonus
total = -50% the employee lose 75% of the potential bonus
and so on

To obtain % of bonus lost, try this:
=(SUM(A1:A3)-1)/2

Hi,
E.