Sales - Value Of Deals That Were Quoted And Closed In Same Week
May 12, 2014
I have a data set of Purchase Order information (each PO on one line) that spans from 2013-YTD and includes the following fields.
P.O. Amount
P.O. Issue Date
P.O. Close Date
I want to know how much P.O. Amount was "Issued" in the same week in which it was "Closed".
I would like to derive the following from that information:
Average of P.O. Amount that is Issued and Closed in the same week (in % and $ terms),
Count of P.O. Amount Issued and Closed in the same week.
I don't know how to aggregate the information to know how much P.O. Amount was Issued and Closed in the same week.
View 2 Replies
ADVERTISEMENT
Oct 14, 2009
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
View 10 Replies
View Related
Jun 9, 2009
I have to produce a report which shows how many sales each of my company's eight branches has sold this week. The problem lies in the fact that my manager wants the week to start on a Friday, so effectively I need to count the number of sales for each branch since the last friday (including Friday but not today, ie today's report would count all sales from 05/06/2009 to 08/06/2009).
Is this possible?
My data is as follows: Branch name is in column P, Date Sold is in column S. I have added a column which is next to Date in column T which has "=WEEKDAY(S2)" to give me the week number which I was using to try and count but failed miserably!
View 9 Replies
View Related
Sep 17, 2012
I have a file with over 20,000 rows that contain a date (Mon~Sun), What I need to do is remove every day that contains Mon~Sat and only keep Sun, this is the code that I have come up with and is working.
Code:
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A" & Lastrow).End(xlUp))3
[Code]....
View 1 Replies
View Related
Aug 24, 2009
I am trying to work out FX P&L by linking trades recorded on a spreadsheet to FX rates depending on which way the currency is quoted. I have attached my spreadsheet to the post. In the Worksheet 'Daily' In Column L, I want to calculate the USD Equivalent for all the trades recorded, by perfoming a simple calculation linked to the FX Rates in the 'Summary' Worksheet.
If on a trade, the Accounting Currency (P&L Currency) Is USD, then I just want the cell to equal the amount in column J. If however the P&L Currency is any other currency, I want it to divide the amount in that field to the corresponding rate in the 'Summary' Worksheet. For example, in Row 108, on the Daily Worksheet, the P&L currency is JPY. I want to divide the JPY amount in J108 by the JPY fx rate in the 'Summary' Worksheet and return that number in L108, so the answer would be $59,353.47.
View 2 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
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 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
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
Mar 27, 2013
I cannot solve with Excel 2010 and I have searched all over for the answer.
I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.
For example, I would like to turn this:
3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx
Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)
I have a feeling some 'countif' formula would work but I am not sure how to do this.
View 4 Replies
View Related
Oct 3, 2012
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
View 9 Replies
View Related
Apr 26, 2008
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder
View 2 Replies
View Related
Aug 21, 2008
Trying to find the sum of all cells in the array described in the formula that are equal to the values inside the quotations. I used this exact (as far as I can tell) formula to find the sum of values that were NOT equal to my quoted values and it worked just fine. Any ideas why formula 'A' will not work but formula 'B' does work? I have a feeling I'm missing something simple here!
Formula A - Does not work:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000="REJECTED"),--('Master Lead Sheet'!$N$2:$N$10000="CONDITIONED"),--('Master Lead Sheet'!$N$2:$N$10000="APPROVED"))
Formla B - Works:
=SUMPRODUCT(--('Master Lead Sheet'!$J$2:$J$10000=$B2),--('Master Lead Sheet'!$N$2:$N$10000"No Answer"),--('Master Lead Sheet'!$N$2:$N$10000"Disconnected"),--('Master Lead Sheet'!$N$2:$N$10000"Wrong Number"),--('Master Lead Sheet'!$N$2:$N$10000"EMAILED"),--('Master Lead Sheet'!$N$2:$N$10000"needs to be emailed"),--('Master Lead Sheet'!$N$2:$N$10000"Refund"),--('Master Lead Sheet'!$N$2:$N$10000"REFUNDED"))
View 9 Replies
View Related
Jan 19, 2010
I am new to VBA & not sure of the full understanding of code copied from a workbook which worked on the same principle but with Monthly (12) tabs. I thought if modified to show weeks, the macro would be able to locate the current week tab & day/date within - but upon opening, the cell stops at WK19 & column O - rather than WK43, Column N (which changes daily).
Sub Auto_Open()
week(1) = "WK1"
week(2) = "WK2"
week(3) = "WK3"
week(4) = "WK4"
week(5) = "WK5"
week(6) = "WK6"
week(7) = "WK7"
week(8) = "WK8"
week(9) = "WK9"
week(10) = "WK10"
week(11) = "WK11"
week(12) = "WK12"
week(13) = "WK13"
week(14) = "WK14"
week(15) = "WK15"
week(16) = "WK16"
week(17) = "WK17"
week(18) = "WK18"
week(19) = "WK19"
week(20) = "WK20"
week(21) = "WK21"
week(22) = "WK22"
week(23) = "WK23"
week(24) = "WK24"......................................
View 9 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
Dec 11, 2013
I was wondering if there's a way to add a formula to calculate week over week % change automatically every week when I enter in new data. see the attached excel file for reference.
What I would like to have is the ability for the formulas in c5 and f5 to be able to auto-update to the newest week and the previous week's data instead of manually having to update it each week. So if I were to add a new row with data for week beginning 12/2, the formula in c5 and f5 would automatically update to calculate the week over week variance. I tried researching prior to asking the question on this forum, and I think it may be possible to do it using the index match function, but I'm not sure how to apply it in this case.
View 3 Replies
View Related
Aug 6, 2008
I have a comparison model that looks at two weeks of data. I am trying to get around the deletion and insertion of records week on week. With the code below, I can currently find and correct the deletions and insertions to the list, and then resort the list so that the comparison will work.
Sub CheckForNewProjsRemovedProjects()
Dim MyCell As Range, oCell As Range, NewCell As Range
Dim Rng1 As Range
Set Rng1 = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
' Columns("B:B").Select
Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"C1"), Unique:=True
For Each oCell In Rng1
For Each MyCell In Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
If MyCell.Value = oCell.Value Then................
View 9 Replies
View Related
Mar 6, 2010
I have a running time sheet daily. It has 2 columns for Labor and 2 columns for travel
i.e. travel From / To 1300-1400 calculate 1 hour then travel home 1600-1700 1 hour this is calculated by the date entry 01/02/10 I have another calculation that tracks by the date i.e. 01/02/10 then Next job which all works fine.
The problem becomes how do I calculate a weekly total labor and travel by the date So added another column called weekly hour’s labor and use the Weeknum to determine which week is which day/date so the first Monday in January 2010 is week 2
2 problems
Having many multiple day / date entries are the same date x 7 days Monday –Sunday
(Relies on the date entered and the weeknum) 01/03/10 each line is complete however the dates carry over as does the time
When trying to calculate each row x 3 same date time then the value will be incorrect I need to calculate
Say 9.5 hour labor from the date 01/03/10 not 28.5 hours and then calculate the total weekly hours
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
01/03/10, 9.5 hours labor, 3 hours travel
i do have work and travel times for each job on the same line (separate columns) but I display the total here by date to summarize the totals
i have tried sum products and sumif to avail. I am using Windows XP SP2 with MS Office 2007
how do i calculate weekly hours by date and weeknum ?
Total Work per day
Total Travel per day
Daily....................
View 9 Replies
View Related
Jun 16, 2014
I'm trying to write a formula that will tell me when its week one or week two, week three and week 4 based on a given date of any month.
I'm using weekday formula but no luck.
View 6 Replies
View Related
Feb 5, 2009
I have to download a report every week and have been manually checking the report weekly for changes.
View 5 Replies
View Related
Nov 24, 2009
I have a column where I am convering the Date into a Fiscal week number.
For example 10/6/2009 is Work week 41
Now I want to show October Week 41
I need to add the month and the text "Week" before the week number. what is the formula I use.
View 3 Replies
View Related
May 21, 2009
I have one sheet that shows a list of all vehicle sales for a month: with a customer column and a salesperson column and a gross profit column. I would like to give a printout to each salesperson from a different sheet that only shows that salespersons transactions on it. Can excel parse that information out and list it in order row by row showing each sale for just one salesperson per sheet?
View 9 Replies
View Related
Apr 6, 2007
Is it possible to format cells to convert a date format of month/day/year to = year/week #/day of week? For example, 04/05/07 (April 5, 2007) would read as 7145, (7=last digit of year/ 14 = week number / 5 = day of week....Sunday being the first day of week)
View 9 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 10, 2009
I have data for average turnover per hour of a business and have fitted it to a polynomial (order 6) trendline. So I have for example, running along the x-axis i have 8am-9am, 9am-10am, 10am-11am etc and on the y-axis the average turnover for the relevant hour.
What I would like to be able to do is use the trendline's formula to be able to predict sales for any given day. So if I were to enter the first few hours of turnover on any given day, I would like excel to predict the rest of the day's turnover based on the trendline.
View 10 Replies
View Related
Feb 13, 2009
I want is for the the cover sheet to provide a simplified version of the price list for people to use that will auto complete price to customer once product has been selected.
- how do I create a sublist? so if a user selects a product group from a drop down list in cell b1 then cell b2 will automatically display only the items from that groups subset.
- how do I link prices? If a user selects data from a drop down menu in b2, is it possible to display the price for that item in cell b3?
View 4 Replies
View Related
Feb 26, 2009
I'm stumped on what I know is a pretty basic problem. Maybe i'm just trying to over think it.
I have a table of sales data...One field is the date it was sold, one field is the amount it sold for. The date field isn't in order and it contains dates over the past 12 months. I need a way to total the amount of sales in each month and not through a pivot table. I am able to count how many entries there are, but I can't find a easy way to do a count of how much was sold in each month.
View 6 Replies
View Related
