1. Due to the recent site upgrade, the iPadForums (iPF) App will no longer work. The new iPadForums.net website has been designed from the ground up to work perfectly from any mobile, tablet, or desktop computer using the built in internet browser. Create a shortcut to iPadForums.net on your home screen by following these steps: Create an icon for iPadForums.net

Assists with a formula

Discussion in 'iWork Forum' started by pstar, Feb 28, 2012.

  1. pstar
    Offline

    pstar iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Need help with a numbers formula. Actually two formulas. The first is to determine profit after paying 7% royalties so a cost column a sales column and a column showing profit after royalty. Then a formula that would allow a user to enter a specific after 7% royally profit and have the sales price auto adjust. Any advice would be greatly appreciated.
  2. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    Formula 1
    SalesForm_1.jpg
    The parentheses in this formula are not necessary. Like the second formula the priority of calculations (multiply before addition) will take care of it. I included them for my own reference at first, and did not bother to remove them.

    Formula 2
    SalesForm_2.jpg
  3. pstar
    Offline

    pstar iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I have figured out my first formula

    image-1546517148.jpg



    image-577098130.jpg

    I did a poor job explaining what I was looking for I hope these two picture help......... The second formula that I needed he with would have cost filled in and % filled in and then numbers would calculate the sale price taking into account the 7% royalties of the top...... I'm not even sure where to begin????

    Thanks
  4. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    Well, I can help you begin.

    PR + Profit
    PR% = Profit Percentage

    You've already got the formula for Profit: PR = SELL - COST - (SELL x .07)

    Take the the formula for Percentage: PR% = PR / COST

    Substitute the formula for PR in the PR% formula for: PR% = ((SELL - COST - (SELL x .07)) / COST

    Now solve for SELL = ???


    Unfortunately my algebra is no longer up to the task. I tried, three times. The closest I could get was:

    (PR% x COST) + COST = SELL - (SELL x .07)

    SELL needs to be reduced to a single variable on the right side of the formula (and no entry on the left). I can almost feel my old math teacher beating me over the head with the solution, but only the scars from the tuler remain. Then again, I've probably made a basic mistake somewhere along the line.

    SalesFormula.jpg

    Oh well, it was fun trying to relive high school math, in a masochistic sort of way. ;)
  5. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    I restated the problem to something that looked familiar to me. (SELL x 1) - (SELL x .07) and fed it into Wolfram Alpha and got SELL x .93

    So your formula (providing I made no other mistakes) should be:

    = (PR% x COST + COST)/.93

    I'm going to test it now.
  6. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    Ok, it seems to work. At first I though I'd blown it, because the numbers didn't match your example, so I recreated the your first table and ran it backwards. Looks like whatever you are using to computer percentage in your first screen shot is incorrect (or maybe it is not a formula).

    SalesFormula_Final.jpg
  7. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    For whatever reason I continued to play with this until I had an almost usable spreadsheet. If you want to download it and compare, or get a better look at what I did (and tell me I still didn't get it right :D ), you can get it here.

    Ooops. That did not work. Back to good old reliable Box.

    http://www.box.com/s/4qalqu1lgsom9048pl9c


    Edit: Unable to leave things alone. I fixed the percentage averaging formulas at the bottom, so they don't average the null (0%) entries. Could still cause slight errors if you sold something for no profit. Made the table a bit longer. Added a mostly for fun column that shows whether you meet, exceed, or miss a sales goal.

    I envision this being used for something like eBay tracking, though there are probably better tools on eBay. Or maybe tracking garage sale profits.

    But I am done, really done, and going to bed. I mean it this time. 12:37 AM here.

    Ok, 12:57 AM, and am shutting off computer NOW.
    Last edited: Feb 29, 2012
  8. pstar
    Offline

    pstar iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I own and manage a franchise, being a franchise I owe my home office 7% off the top. Sometimes when pricing needs to be competitive i have to run a bunch of numbers this worksheet would be a huge asset.
    The math only works one way. Meaning 21.47 plus 7% is not the same as the reverse........ 22.97 minus 7 % is not 21.47. This makes things complicated and it doesn't seem to become easier with my iPad and numbers.... . I'm not even sure at this point it's possible to do. ? I don't believe the screen shots I've seen solve the issue. I hope with the recent clarification and this extra screen shot will help clear up my dilemma. Thanks a ton for the effort put forth.

    image-2713190957.jpg
  9. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    But the last formal does work. Notice that it does not use the .07 royalty figure.

    SELL = (PROFIT% x COST + COST)/.93

    I double checked it by running several sets of numbers through it, then plugging the results back into the other equations that computed your profit percentage. Which is:

    PROFIT% = PROFITAR/COST

    or the long way

    PROFIT% = (SELL - COST - SELL x .07)/COST

    I can't explain exactly why it works, because I plugged the problem equation into Wolfram Alpha for a solution, and got the .93 constant back. I suspect it takes a bit of calculus to solve the equation properly. However if you download the spreadsheet I linked, and play with it (and look at the equations) I think you will find that it works.

    SalesSheet.jpg
    Last edited: Feb 29, 2012
  10. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    Edit: Ok, fixed. I had the Mark Up formulas wrong.

    Edit: The Mark Up computation sheet is not working properly. Have to do some trouble shooting. Arrggg.


    Ok, I've revised the worksheet to be more inline with what you want to do. Don't ask me why, guess I was just bored. :)

    It now consists of three tables. Each table includes these fields.

    Cost = Cost of item
    Sell = What the item does or will sell for
    Mark Up = Percentage the item is marked up from Cost
    Profit AR = Profit after royalties and Cost subtracted.
    Profit % = Percentage of profit after royalties and Cost are subtracted.

    Table one computes all other fields based on Cost and Sell price.

    Table two computes all other fields based on Cost and Mark Up (Percentage)

    Table three computes all other fields based on Cost and Profit %

    To the best of my knowledge all formulas work. They can be double checked by entering the output of one table into the input of the other and making sure that all values match.

    There are probably a few formatting mistakes I didn't catch.


    ProfitWksht.jpg
    Profits Worksheet.numbers
    Last edited: Mar 1, 2012
  11. pstar
    Offline

    pstar iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I have downloaded it but I cannot get it to function, it seems to be just a picture?
  12. pstar
    Offline

    pstar iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I figured out how to open it in numbers......
  13. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    Good.

    My brain kicked in early this morning and I figured out how to get past the part of the equation I was stuck on, and I got the same result Wolfram Alpha gave me. Heck, I figured out how to simplify the equation a bit more. It did not take calculus after all. If you are interested I can show the work. It might come in handy if your royalty rate ever gets changed.
  14. Gtatler
    Offline

    Gtatler iPad Fan

    Joined:
    Dec 5, 2011
    Messages:
    288
    Thanks Received:
    22
    Trophy Points:
    18
    Location:
    UK
    Ratings:
    +26 / 0
    Silly question, but may I ask which apps you are all using ? I don't recognise any of the screen shots.
  15. Rockbottom
    Offline

    Rockbottom iPad Fan

    Joined:
    Jun 15, 2011
    Messages:
    112
    Thanks Received:
    7
    Trophy Points:
    18
    Location:
    Montreal Canada
    Ratings:
    +7 / 0
    Numbers is the name of the app. With fancy formatting.

    Sent from my iPad2 3G 32 gig (not JB'ed) / using iPF
  16. Rockbottom
    Offline

    Rockbottom iPad Fan

    Joined:
    Jun 15, 2011
    Messages:
    112
    Thanks Received:
    7
    Trophy Points:
    18
    Location:
    Montreal Canada
    Ratings:
    +7 / 0
    Twerppoet mentionned Wolfram Alpha. I never heard of it before and it is intriguing. I noticed there is an app but the developer web site helps in understanding what it is. Very fascinating.

    Sent from my iPad2 3G 32 gig (not JB'ed) / using iPF
  17. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    It's a handy site and app. I consider it one of the indispensable reference apps for the iPad. It started out geared towards math equations and terms, but has expanded into much much more. Combine it with a good dictionary/thesaurus and Wikipedia, and your iPad can substitute for a large chunk of a college library's reference section.
  18. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    As Rockbottom said, most of the screen shots are from Numbers. Everything else I posted that is not Numbers, or is Numbers but with scribbling on it, was done with Skitch; a free app for scribbling on screen shots, maps, websites, and other stuff. Or, as in the case of the scrawled equations, just a blank page.

    I use it a lot, and so do several other members. It's a handy (quick) way of showing things that are difficult to describe.
  19. pstar
    Offline

    pstar iPF Noob

    Joined:
    Feb 28, 2012
    Messages:
    7
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    I would really like to see the formula that you have. The percentage shown is different then what I normally get..... ? I'm not sure how or why and trying to wrap my mind around it has cause major brain cramping!
  20. twerppoet
    Online

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    15,986
    Thanks Received:
    1,853
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +1,998 / 1
    Upon further reflection, I think I'm computing the % of Profit wrong. I've been calculating it as a percentage of the Cost; and after thinking about it I'm betting you are after a the % of the Sell price. Yes?

    Let me go over it one more time, based on that more reasonable assumption and see what I come up with.

Share This Page

Search tags for this page

percentage formula

,

quickoffice formula

,

quickoffice formula to subtract

,

subtract in numbers

,

what formula in mac numbers to calculate profit