Simple? Percentage forumla

**Luthier** · 10-14-2012, 10:21 PM

I have a formula 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%

Let me explain the purpose of it and perhaps that will make my intention a bit more clear.

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.

Perhaps one way to achieve this, and I'm not sure how to write the formula, would be to say if there are more negative numbers from F5:F7 then add only the negative numbers, however if there are more positive numbers in this range only add the positive. I think that would achieve what I'm trying to do.. perhaps not.

Ideas?

**FDibbins** · 10-14-2012, 10:34 PM

when you add -35+35-30 you are going to arrive at -30, no matter which way you look at it.

if you want to find out how much was "lost" and how much was "gained", try using 2 different formulas...
=SUMIF($F$5:$F$7,">"&0) for the positive values
=SUMIF($F$5:$F$7,"<="&0) for the negative values

**Luthier** · 10-14-2012, 10:42 PM

I do understand the math.... Let me ask it this way

I know I can use a =SUMIF(F5:F7,”<0″) and that will only add the negative or positive cells depending on the <0, >0.

Is there a formula that will say that if the sum of F5:F7 is less than Zero then only add the negative cells, but if the number is greater than Zero then add only the positive cells? This formula will be just in one cell but handle both possible outcomes.

**Luthier** · 10-14-2012, 10:48 PM

Here is a small screenshot of the table in question with two possible outcomes. I manually entered the total (-65%, and 70% respectively). I'm hoping this will help illuminate a bit on what I'm trying to achieve.

neg.jpg

pos.jpg

**FDibbins** · 10-14-2012, 10:54 PM

next time, please dont upload pictures, upload the workbook.

try this, based on your original range

=IF(COUNTIF(F5:F7,">"&0)>COUNTIF(F5:F7,"<"&0),SUMIF($F$5:$F$7,">"&0),SUMIF($F$5:$F$7,"<"&0))

**Luthier** · 10-14-2012, 11:05 PM

Many thanks that indeed worked. If you don't mind could you explain what you wrote there so I can understand and perhaps use that knowledge in the future rather than just copy and pasting (which I did

)

**FDibbins** · 10-15-2012, 12:15 PM

=IF(COUNTIF(F5:F7,">"&0)>COUNTIF(F5:F7,"<"&0),SUMIF($F$5:$F$7,">"&0),SUMIF($F$5:$F$7,"<"&0))

the 1st part checks to see if the qty of the pos (>0) is greater than the qty of negs. if it is, it sums the pos, else it sums the negs

hope that helps, andtrhx for the star