1 2 Previous Next 20 Replies Latest reply: Jan 31, 2013 8:58 PM by Yellowbox
kaffholter Level 1 Level 1 (0 points)

OK I am trying to create a percentage formula for the following.

 

B8 is 638.00 and C8 is 731.00  I want the result to tell me what the % is -/+

 

I am not getting how to create this formula.  I am new to this.


Mac Book Pro, Mac OS X (10.5.6)
  • 1. Re: Need to create a percentage formula
    Wayne Contello Level 6 Level 6 (13,615 points)

    The percentable of one value to another is the ratio. 

     

    B as a percentage of C is B divided by C so...

     

    638 divided by 731 is

    638 / 731 = 0.87278

     

     

    D8=B8/C8

     

    to enter this in a cell select the cell D8 then type "=B8/C8" then enter

     

     

    If you want the percent increase of one value compared to another:

    percent increase of B to C then it would be:

    (B-C) / C

     

    to enter in cell D8 type "=(B8-C8)/C8" then hit enter

  • 2. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    Ok I know for a fact that I should be getting 100% increase on the outcome of this.

     

    So let me give a little more detail and a different row.

     

    In 2012 the town added a postion and the cost was 1500.00 (b7) This position did not exist in 2011 (c7).  This resulted in a 100% increase to the budget. 

     

    Thanks

     

    Kat

  • 3. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    Thanks so much, not sure what I was doing incorrectly but it is working now.

  • 4. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    Ok I applied this formula to the situation with the 100% increase of 1500.  My result was a 235.11% increase  This is not correct.  Now I have $0 in cell c7 would that have anything to do with my result?

  • 5. Re: Need to create a percentage formula
    Wayne Contello Level 6 Level 6 (13,615 points)

    what is the old number and the new number?

  • 6. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    ok the old number was 0,(c7) the position did not exist in 2011.  In 2012 the town voted to have a Health Officer and pay them 1500.00 a year (b7)  When I plug in the formula for the increase percentage it tells me that it should be 235.11%  How is that possible?

  • 7. Re: Need to create a percentage formula
    Wayne Contello Level 6 Level 6 (13,615 points)

    the percent increase is:

     

    (New - Old) / (Old)  = (1500 - 0) / 0 = this is NOT a defined value (as divide by 0 is ilegal)... so I don't know how you get 235.11

     

    Can you describe what you got?

  • 8. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    HMMM I am not sure I understand what you mean by "describe what you got"?

     

    I used the formula that you gave me for the other scenario to get an increased % and now that I know that 0 is not a defined value and is illegal.  I am not sure why it gave me a 235% increase.  So there is no formula for me to use in this situation.  Everything els is working fine.  I will just put in 100% in the cell and move on to other things

     

    Ok How does one create a formula if the variable are all the same.

     

    Ok I have three line of information that I want to do one formula for. 

    b15-b17 and c15-c17

    I want to do a simple subtraction for all three rows, or else this will take be for ever to get this finished for the next budget meeting. 

    The answers will go in cells d15-17

     

    I have tried to look this up but not sure what criteria to do my search under.

    so I am going to want this set up like so

    b15-c15 goes into cell d15

    b16-c16 goes into cell d16

     

    Thanks for your help

  • 9. Re: Need to create a percentage formula
    Wayne Contello Level 6 Level 6 (13,615 points)

    Can you post a screenshot of the table in question?

  • 10. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

     

    I would like to do groups of these

     

    OK it did not take and I do not remember how to do a screen shot in Mac

  • 11. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    Ok I am going to bed.  It is almost midnight on the east coast.  Will continue this in the morning.

  • 12. Re: Need to create a percentage formula
    Barry Level 7 Level 7 (29,180 points)

    Hi kat,

     

    "In 2012 the town added a postion and the cost was 1500.00 (b7) This position did not exist in 2011 (c7).  This resulted in a 100% increase to the budget."

     

    Wrong.

     

    An increase from zero budget to a budget of any amount cannot be expressed as a percent increase.

     

    Any increase expressed as a percentage is expressed as a percentage of the starting amount.

     

    Suppose the budget for this position for 2011 was 750.00.

    100% of that amount is 750.00.

    A 100% increase from the 2011 budget would then be the original budget (750.00) plus 100% of the original budget (750.00), and the new budget would be 1500.00.

     

    But the 2011 budget for this position was 0.00.

    100% of nothing is nothing.

    A 100% increase is nothing plus nothing.

    A 200% increase is nothing plus (nothing x 2).

    A 1000% increase is nothing plus (nothing x 10)

     

    "HMMM I am not sure I understand what you mean by "describe what you got"?"

     

    I think a clearer expression would be "show how you got this result (235%). Copy the formula and paste it into your message. show the values that were in each of the cells referenced in the formula." Like Wayne, I'm mystified by this result.

     

    "now that I know that 0 is not a defined value and is illegal."

     

    0 is defined, and has been for centuries. 0 is not illegal. But division by 0 IS 'illegal' in the sense that it is not permitted in mathematics because its result is undefined. Simple example:

     

    I have three halloween treats left in the bowl. Zero ( 0 ) children come to my door calling 'trick or treat.' If I share the treats equally among the children, how many must I give to each child so that I have no treats left over?

     

     

    "I will just put in 100% in the cell and move on to other things."

     

    Ummm... 'I don't understand this, so I'll just pull a number that 'looks right to me' out of the air, plug it in, and move on." How does putting a wrong value into your budget make the budget turn out right?

     

    To your next topic/question:

     

    " I am going to want this set up like so

    b15-c15 goes into cell d15

    b16-c16 goes into cell d16"

     

    This works pretty much exactly as you've written it.

     

    In D15, enter: =B15-C15

    Picture 28.png

    Grab the Fill control handle (small circle) and drag down two rows to fill the formula into D16 and D17.

    Picture 29.png

    Results will appear in each cell as the formula is filled into that cell.

     

    Regards,

    Barry

  • 13. Re: Need to create a percentage formula
    kaffholter Level 1 Level 1 (0 points)

    Hi Barry,

     

    I have no idea how I came to the result of 235.11%.  LOL  However if there was no position for this in 2011.  Then the increase in the budget would be 1500%, right?

     

    Thank you for all the help.  I greatly appreciate it. 

  • 14. Re: Need to create a percentage formula
    Barry Level 7 Level 7 (29,180 points)

    Hi kat,

     

    "However if there was no position for this in 2011.  Then the increase in the budget would be 1500%, right?"

     

    I don't know how to state this any more clearly than what I posted previously:

     

    "Wrong.

     

    "An increase from zero budget to a budget of any amount cannot be expressed as a percent increase.

     

    "Any increase expressed as a percentage is expressed as a percentage of the starting amount."

     

    The position did not exist in 2011, so the budget for the position was nothing.

     

    100% of nothing is nothing.

    1500% of nothing is nothing.

    7,000,000% of nothing is still nothing.

     

    You are looking for the percentage increase that will make this equation correct:

     

    starting-amount + (starting amount * percentage) = 1500

     

    For a starting amount of 1000, the numbers are:

     

    1000 + (1000 * 50%) = 1500

     

    For a starting amount of 750:

     

    750 + (750 * 100%) = 1500

     

    For a starting amount of 500:

     

    500 + (500 * 200%) = 1500

     

    But your starting amount (the 2011 budget for this position) is 0

     

    0 + (0 * ???%) = 1500

     

    What number can be used in place of ???% that will make that equation correct?

     

    Regards,

    Barry

1 2 Previous Next