+ Reply to Thread
Results 1 to 4 of 4

Caculate profit and margin from cost and revenue, per sale, cumulative

  1. #1
    Registered User
    Join Date
    02-28-2019
    Location
    TN
    MS-Off Ver
    16
    Posts
    2

    Caculate profit and margin from cost and revenue, per sale, cumulative

    Hi all, screenshot is attached to help you follow along.

    I am trying to make a formula or series of formulas to help my small business. What I want to to is mark sold in the SOLD column and type in the income and have my profit and margin populate. Further, I want to keep a running profit and margin. I know I can average the margins and sum the profits. I have made a formula that does the job but when I apply it the the whole sheet. It subtracts my cost from 0 and gives negative numbers and throw the sheet off. If you are still following me at this point, is there anyone that could help?
    Attached Images Attached Images

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Caculate profit and margin from cost and revenue, per sale, cumulative

    Welcome to the Forum bassmaster !

    As a new member, please take the time to review our rules. There aren't many, and they are all important. Rule #1 calls for good titles. I have updated your title since it's your first post.

    In your data, what is Gross? You only have Cost, so we can't calculate gross margin vs. net margin, so it's not clear what you mean by Gross.

    You don't really need to have a Sold column, since entering a value in Revenue means it's sold.

    In rows 2 and 4, how can you have a cost of $0?

    If you enter revenue, margin (profit as a percentage of revenue) is
    =IFERROR((E2-D2)/E2,"")
    It will be blank until you enter revenue.

    Total Profit is
    =SUMIF(E:E,">0",E:E)-SUMIF(E:E,">0",D:D)

    Total Margin (percentage) is
    =J1/SUMIF(E:E,">0",E:E)
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    02-28-2019
    Location
    TN
    MS-Off Ver
    16
    Posts
    2

    Re: Caculate profit and margin from cost and revenue, per sale, cumulative

    Thank you for this. I realize things were a bit confusing. I have one more question. Your formulas helped hide unfilled data in Margins column. What is needed to do the same in the profit column?
    Attached Images Attached Images

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Caculate profit and margin from cost and revenue, per sale, cumulative

    You didn't show the formula you are using but it's easy to deduce, so this modification:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Determining Margin Formula, I know cost and margin desired.
    By Draftdude in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2018, 06:08 AM
  2. help with a profit margin formula please
    By lynchburgbull in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-07-2016, 07:47 AM
  3. Replies: 0
    Last Post: 01-21-2014, 12:04 PM
  4. [SOLVED] Profit margin formulas
    By Allgermanparts in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-25-2013, 06:30 AM
  5. easy one - margin % from revenue and profit?
    By equinox101 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-13-2013, 05:52 AM
  6. Calculating sale/cost/margin smartly
    By AManly in forum Excel General
    Replies: 3
    Last Post: 05-19-2011, 06:33 PM
  7. Grouping Revenue,Cost,Profit Data (Pivot Table 2007)
    By easycapital in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-01-2008, 08:56 AM

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