+ Reply to Thread
Results 1 to 4 of 4

Calculating sale/cost/margin smartly

  1. #1
    Registered User
    Join Date
    02-03-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Calculating sale/cost/margin smartly

    Sale = A1
    Cost = B1
    Margin = C1

    I know to calculate Margin its' C1=1-(B1/A1) and to calculate Sale its, A1=B1/(1-C1), and I can do that in separate columns. But...... I'm tasked with trying to find a way so that I can do it in three columns. As in if I change cost then margin recalculates or if I change margin then sale recalculates. I'm thinking that this is going to cause a circular error but my boss says there's got to be a way to do it, figure it out. Any suggestions?

    Alex

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Calculating sale/cost/margin smartly

    You either need extra columns or it would require VBA.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor ron2k_1's Avatar
    Join Date
    09-30-2009
    Location
    Not the USA
    MS-Off Ver
    Excel 2003, 2007
    Posts
    606

    Re: Calculating sale/cost/margin smartly

    Yep! Helper columns or..., with my amateur VBA skills I'd do it like this:

    Open a new workbook, and I assume that Sales value is in A1, Cost is in B1, and Margin in C1,

    Right click on the sheet tab and click on "View Code" and paste this:
    Please Login or Register  to view this content.
    The play around with the figures on A1 to C1, if you require a whole range to do that then it's easy to change.

    EDIT: Okkkkk! I just noticed you use Mac Excel, not quite sure whether VB Language is the same...
    Last edited by ron2k_1; 05-19-2011 at 04:27 PM.
    Ron
    Knowledge is knowing that a tomato and pepper are fruits. Wisdom is knowing whether to put these in a fruit salad

    Kindly

    [1] Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
    [2] Mark your post [SOLVED] if it has been answered satisfactorily by editing your original post in advanced mode.
    [3] Thank (using the little scale) those that provided useful help; its nice and its very well appreciated

  4. #4
    Registered User
    Join Date
    02-03-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Calculating sale/cost/margin smartly

    I'm actually not using Mac Excel and at first was curious on how you "noticed" that I was, but I'm guessing it was by how I entered my formulas on the OP. Which I'm finding was not correct, and explains why my calculations are not cross checking correctly. I didn't realize they would be different based on the OS. I'm currently trying to find the correct formulas. Why is it the simple things give the biggest headaches.
    I appreciate your VBA snippet but I am currently not versed in that. That will be my next research.
    Thanks again,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1