# Formula Needed For Calculating Profit On Sales

Dec 14, 2012

In my spreadsheet, I have data for each sale in each row. Column C is the buy price, and column D is the sell price. I need column C to be subtracted from column D to get the profit into column E so that I can AutoSUM any cells in column E for total profit over any time period. The problem I'm having is that in columns C and D, I have either a dollar sign in front, or the letter m following without a space (ie \$45.00 or 150m). Many cells in column C also contain another word following the buy price, and many cells in column D have a date following the sell price (ie \$32.00 12/11 or 42m buyout).

Examples of how my spreadsheet looks:
A1: 12/13/12 B1: Item #1 C1: 100m D1: 152m
A2: 12/14/12 B2: Item #2 C2: \$20.00 D2: \$35.00
A3: 12/15/12 B3: Item #3 C3: 90m bid D3: 120m 12/16

So looking at my example above, I would need cell E1 to automatically show "52m", cell E2 "\$15.00", and cell E3 "30m"

How I can get a formula for all of column E that will give me my profit for both \$ and "m" for each item (row). I have tried the SUM function but just keep getting the #VALUE! error, it does not ignore the text like many people say it should.

## Calculating Price Based On Profit

Jun 26, 2012

I'm using a spread sheet to determine what I should charge customers. I want to make at least \$300 profit on every job. If my profit is not at least \$300 then I must up my price until profit reaches \$300. Currently I'm manually adding a percentage (Factor) to the original price until I reach \$300 profit. In the image below:

A is the original price
B is the factor by which I multiply the original price to increase it
C is the new figure which is reached after multiplying (1+B2)*A2
D is the costs related to that job (I enter these manually on a separate sheet within the workbook)
E if the profit (C-D)

I use a factor in B because when I increase the price, then I have to increase several parts of the job by the same percentage so I multiply each part by this factor to increase the price of each by the same percentage.

Now what I would like to do is if E2.

## Calculating Sales Price

Jul 18, 2008

using Excel 2002 on XP.

My partner and I are selling products. He gets 5% of the sales price, then I get the rest. But I want to make at least \$2 on every sale. So, let's say the item cost us \$50. If he wants 5% off the top, and I want at least \$2, how do we calculate what to sell it for?

I tried the following, but it didn't work:
\$50 (cost)
+ \$2 (my profit)
+ 5% (partner's profit)
------
\$54.60 (sales price)

It doesn't work because I end up with \$1.87.
\$54.60 (sales price)
- 5% (partner's profit)
- 50 (cost)
------
\$1.87 (my profit)

I've tried other things, but I always end up under \$2. Is it possible to calculate this? or do I need to have a percentage for myself? If Excel can't do it, do you know of any calculators out there than can?

## Formula To Calculate Sales Tax From Total Sales

Jan 4, 2005

I have created a chart on excel for us to track daily sales but also to figure sales tax so we know what to send the IRS each month. We have been figuring the sales tax ourselves and
filling in the chart on excel but I would like to create a formula that
automatically does it for me based on total sales.

## Excel 2007 :: Calculating Sales Close Rate?

Jun 13, 2014

I want to set up an Excel spread sheet where I can put my total number of sales appointments for the month and the percentage of those I closed. I've done this before, but for the life of me I can't remember the formula I used & I know it's a very simple one! I'm using Office 2007.

## Finding And Calculating Highest Trending Item By Daily Sales?

Apr 20, 2012

I have a very large spreadsheet with daily sales for over 140 different items, over the past 2 years.

This is how my data looks like:

ITEM NAME 4/1/12 4/2/12 4/3/12 etc
item1 10 15 500
item2 5 0 3
item3 0 0 5
etc

I'm trying to build a formula that allows me to see which item is the highest trending over a period of time I input.

I figured out the way how to select data between specific Start and End dates, but don't know how to interpret "trending" in a formula.

I was thinking maybe highest jump from beginning to end of interval, but what if it's a new item that was launched halfway through the interval and does poorly throughout, that would still be "trending" under that rationale....

## Calculating Average Dollar Increase In Sales To Running Total

Jun 7, 2014

I have sales numbers which is in the form of a running total dollar amount and I would like to keep track of the average increase in dollars.

So basiclly if the Jan total is \$100 in column a, the Feb total is \$150 in column b, and the March total is \$200 in column c. I would like a formula that will tell me that the average increase in dollars is \$50.

## Precentage Profit Formula

Apr 21, 2009

In cell "C4" how would I write the formula to calcuate my precentage profit based on the given cost and sell pricing.

## How Do I Write Formula To Show A Profit Margin

Dec 1, 2008

I need to work out the profit margin from goods i sell.

I have cost A2
Pack size A3
Net cost A4
Tax A5
Gross price A6

I need to work out the margin between A5 and A6.

## Finding Current Month Total Sales Using Current Month To Date Sales In Formula / Macro?

Aug 20, 2013

Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.

The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.

## Formula Needed That Changes According To A Specific Day

Oct 12, 2008

I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep

## Formula To Sum If 2 Different Factors Are Needed

Apr 9, 2014

I have a big list with names, money spent and date. I need somehow with a formula to do:

Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.

Name
Money

Date

Daniel
5

01.03.2014

[Code] ........

I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.

## Sales Summary Sheet - Consolidate All Data In Sales Order Number Wise

Jan 5, 2012

I have an excel sheet which has 70 sheets in it...All of them are numbered but not in a sequential manner (eg : 210,211,201,202,215 etc..).The numbering is nothing but the sales order number....

All the sheets have same data (i.e Column A contains Product Sold, Column B contains Sale Value)...The thing is i want to consolidate all the data in sales order number wise. i.e.

Column A Column B
201 \$200
202 \$300
203 \$450

## Specialized Average Formula Needed

Jan 14, 2009

In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.

## Determining Top Contributors To 50% Of Sales Based On Cumulative Percent Of Sales

Jul 2, 2009

I am trying to determine the top contributors to 50% of sales based on cumulative percent of sales (see attached file). I can determine if percent of sales is less than 50%, but I need to include the person that pushes the group of top performers over the 50% mark.

## Sales Report From Sales Data Sheet If Select Date

Feb 4, 2013

I wanted to get the data from data sheet where i have entered the data manually for sales what have done

Now I wanted is when i enter date i should get the product and price in other sheet

Example:

If I enter date in a1 cell

I should get the detail of product in b1-b10(if i sold 10 item )which is in data sheet

And c1 - c10 price (if i sold 10 item @that price)

## Pivot Table: Calculate Percentage Of X Sales To To Total Sales

Feb 20, 2008

See the attachment. I want the percentage of Car Sales to total sales of different countries automatically.

## Using Rolling 12 Month Sales And Minimum Monthly Sales?

Oct 17, 2013

I have a sales level that I need to track...My rolling 12 months' sales must be \$85,000 and my currently monthly sales must be \$7,000. I have a sheet that tracks the \$85,000 and tells me what I need to achieve that, but I haven't figured out how to include the \$7,000 monthly minimum....

The chart below is what I have. So for example, this month it's telling me I only need to sell another 3016.46 to hit the \$85,000 rolling 12, but I actually need to hit \$4821.79 to meet the \$7k minimum.

Actual Rolling 12 Goal
Sep 2012 5,367.24 73,663.30
Oct 2012 5,649.93 69,496.28
Nov 2012 14,163.38 73,451.30 [code]....

## Formula Needed That Identifies Last Time Product Run

Jan 21, 2014

Basically I have in the first column, different products (lets call them a, b,c and d). in the second column is a counter which starts at 1 and continues counting down the column.

In the third column I need to identify if it is the last time the product has been run. i.e there are no more duplicates of product a further down the 1st column.

I have attached an example : example.xlsm‎

## Update Formula Needed For Multiple Workbooks

Feb 5, 2012

I'm in need of some VBA code to control the updating of information from 16 active workbooks to a master workbook and vise versa. I would like it on command as it slows down the use of the workbooks when they calculate after every change of data as they are being used. These workbooks are connected over a company intranet and are used 24/7.

The desire is that cells in the master workbook will read specific cells on each of the other workbooks and complete a "map display" of the data. The data is not in a column or row range side by side but scattered across the sheet to form a map when data is entered.

I've tried using code to open and close the workbook with on time events but that hasn't worked.

## Formula Needed To Separate Names From Numbers

Sep 21, 2012

I have a phone list set up as LastName, FirstName PhNum all in a single column. The problem is, some last names have a space, some phone numbers are in the xxx-xxx-xxxx format while others are just an extension like xxxxx.

Example:
Smith, John 519-123-4567
Van Smit, Joe 12345

What can I use in a formula to extract the numbers so that I can end up with 2 columns, one with names, the other with numbers.

## Formula Needed To Count Cells With Data

Jul 12, 2007

formula to calculate how many cells in a column contain data of any kind. The data might be a number, a word, or even a symbol, such as "):>:)" for example. The range will change from time to time. For example, one day it might be a2:a4956. A few days from now I might delete a few rows from the top, such as rows 2 through 26, so then the range would be a2:a4931. Then I might add rows at the bottom with data that I need to be counted. I dont want to have to change the formula evertime I add or delete rows with data. I do not want the formula to count a blank space created by the space bar as "data." One formula I used sorta worked, but if the cell appeared empty, but had been cleard out by pressing the space bar instead of the delete key, the formula counted it as a cell with data.

## Formula Needed Based On 2 Conditions And Variable Row Reference

Jan 9, 2014

I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.

The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.

1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.

The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.

Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.

## Summarize Monthly Sales From Daily Sales

Jan 5, 2010

I have daily basis monthly sales. Now I want to summarize into monthly gross. Pls look attached file. I am looking for a formula to summarize January daily sales from date 1st to 31 st as of just January and and sum of each day gross.

## Formula For Number Of Perfect Ordered Needed To Achieve Certain Target

Jun 11, 2014

I was working on a chart to calculate my Amazon seller rating to see how many more perfect order I need to hit my next target grade. Here is how the calculation works.

Order with no problem gets 100 points per order
Minor problem gets 0 point
Moderate problem minus 100 points
Severe problem minus 500 points

<84.5 Fair
<96.5 Good
<98.5 Very good
Above is Excellent

Total points / total orders = final grade (or percentage)

As of now i got : 53800 (points) / 624 (orders) = 86.22(%)

Now that I know what grade I've got so far and I also want to know how many more perfect orders I need to achieve my next targets or in case some bad orders come by and how far they pull me down.

Attached is my work sheet so far and I'm just missing the calculation for the orange shaded part.

Amazon seller rating calculator.xlsx‎

## Formula Needed To Copy Same Set Of Data From Cells And INDEX Into A List

Nov 27, 2012

I have a list of data in columns Q R S and i need to in between each line insert the data from columns D E F underneath and then underneath that the data from G H I

so bascially i have a list at the moment like this

Q
R
S

[Code]....

There is 2 sheets in total , 1 i need to inc 1-1 draw as above and the other sheet to be exc the 1-1 draw . I have attached an example of what i am trying to achieve . can offer as i have had this on excelforum.com with over 150 views and no solution

## How To Add Monthly Sales Data Using Formula

Dec 29, 2013

How can I add data to the monthly sales section in the workbook? I can't seem to figure out the code.

mockup.xlsx

## Sales Commission Formula Required

Jan 14, 2009

I have a new sale structure to put in place the commission is paid in the following way:

below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )

I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.

## Formula In A Cell That Configures Sales Tax

Jun 1, 2007

I have a formula in a cell that configures Sales Tax. How do I add to my existing formula so that if someone is tax EXEMPT to not display or calculate ANYTHING in the Sales Tax Cell? I want to add this to my existing formula in my sales tax cell: IF(Y33="YES") and then I want it to override any existing formula and display nothing in that cell at all.

## Formula's For Calculate Sales Commissions

Jan 2, 2008

I am trying to decipher how to calculate commissions for my sales reps. I have made just a simple spreadsheet to give you an idea of what I am doing. I have tried to us an IF formula but I think there are too many options( I have 9 reps). Basically I pay them either 10 or 15% so I need a formula to take the sales price - cost times their apporpriate %.

AgentSales Price CostComm Pd
AS150 75
JK255 185
JD325 250
JD125 50
AS50 10
AS50 10
AS335 250
JW75 25