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?

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.

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.

Il 14/10/2012 06:14, Luthier ha scritto:
plinius;1606335 Wrote:
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.

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.

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.

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.

I believe we are getting closer, but that's still not quite right. Let me give two examples of possible scenarios:

A1: 30%
A2: -35%
A3: 35%

The total in this scenario should read 65%

__________________________________________________ _______________

A1: 30%
A2: -35%
A3: -35%

The total in this scenario should equal -70%


You see for each cell (a1, a2, a3) they represent a grade on a certain criteria that the employee is being graded on. If they perform poorly in one section, and therefore receive a negative modifier, they should still get credit for the positive sections they did well in and get the appropriate credit for that.

I guess one possible solution would be to have the formula say to add the three cells and if the sum is less than Zero only add the negative numbers, and if it is greater than zero only add the positive numbers. I think that would work (although I still don't know how to write that formula)

Here is the link to the spreadsheet. It's generic right now, but the cell I'm wishing to have this function in is F8. Perhaps that will help.

https://docs.google.com/open?id=0B4A...kRGZ1lxUk1SVUk