Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Simple (maybe) percentage forumla
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple (maybe) percentage forumla
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. |
#3
|
|||
|
|||
Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple (maybe) percentage forumla
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. |
#5
|
|||
|
|||
Quote:
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple (maybe) percentage forumla
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. |
#7
|
|||
|
|||
Quote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble with simple percentage formula | New Users to Excel | |||
Forgotten a Simple Forumla - Is Text present in another column | Excel Discussion (Misc queries) | |||
Need help, simple math...from a percentage | Excel Discussion (Misc queries) | |||
Very simple percentage problem | New Users to Excel | |||
Forumla to calculate a percentage | Excel Discussion (Misc queries) |