# 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.

View 6 Replies
ADVERTISEMENT
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.

View 2 Replies
View Related
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?

View 9 Replies
View Related
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.

View 9 Replies
View Related
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.

View 1 Replies
View Related
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....

View 1 Replies
View Related
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.

View 2 Replies
View Related
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.

View 3 Replies
View Related
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.

View 10 Replies
View Related
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.

View 1 Replies
View Related
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

View 4 Replies
View Related
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.

View 7 Replies
View Related
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

View 8 Replies
View Related
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.

View 13 Replies
View Related
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.

View 3 Replies
View Related
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)

View 4 Replies
View Related
Feb 20, 2008

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

View 2 Replies
View Related
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]....

View 6 Replies
View Related
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

View 2 Replies
View Related
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.

View 5 Replies
View Related
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.

View 6 Replies
View Related
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.

View 9 Replies
View Related
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.

View 3 Replies
View Related
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.

View 4 Replies
View Related
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

Grade are evaluate by:

<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

View 3 Replies
View Related
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

View 2 Replies
View Related
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

View 5 Replies
View Related
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.

View 4 Replies
View Related
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.

View 9 Replies
View Related
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

View 9 Replies
View Related