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.
I'm trying to calculate a running total of ticket sales with 5 different groups (adult, youth, senior day, senior night, and child) to where once the number of tickets are bought it will update automatically. I enter the tickets in cells B5-B9 where it then calculates the amount due and the change due. I need to put the running total in cells B18-B22.
View 3 Replies View RelatedI want to calculate a running total (so add a new value to its original value). For example, cell A1 is the one I will be changing daily, and cell B2 is the one that I want a running total in. So if I start with A1 being 5, B2 should be 5. If the next day I type in 3 into A1, B2 should now read 8. However I want to do this for a column length, not just cells A1 & B1. So column B would be the running total of the column A values. If A2 is 3 one day and the next it is 6, B2 should on the second day read 9. And on and on down the columns.
I tried doing this without VBA using iterations, but that didn't work. Every time I did that it would add values going down, which was weird cuz none of the cells referenced the cell above it. I didn't know if they would work since I have multiple sheets in this file. (I'm only calculating the running total in one sheet, the others aren't doing anything special.) I need all the sheets in my file and I didn't know if VBA modules are sheet specific/ if you need to be sheet specific when coding.
Another question I have (since I know nothing about VBA) is how do modules work? Do you need to turn them on in the Excel spreadsheet itself? Or when you save it does it just automatically apply itself to the file its attached to?
On another note, how to do this WITHOUT VBA, that would be like 100x more fantastic.
I have a workbook with each month as a tab Jan 2014- dec 2014. I have a totals page that's has total billings( the sum is adding all the totals of each month). The totals for each month are in different cells based on the number of individual invoices I enter for each month. I have entered jan- march invoices. I would like to put in a formula on my totals sheet that gives me a ytd avg without changing it. ie: d4/3 then next month april d4/4.
View 1 Replies View RelatedIn column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies View RelatedSee the attachment. I want the percentage of Car Sales to total sales of different countries automatically.
View 2 Replies View RelatedI need to do a price increase on an Excel spreadsheet. I need to increase existing prices by 10% and round up to next dollar. I am not sure to to accomplish this in one step on an entire column at one time. I can copy and paste the existing prices into another column and increase them by paste special multiply by 1.1 but not sure how to also roundup the prices.
I have enclosed a copy of one of the pages in the price list so you can see what I am attempting to do. As you can see I need to increase the prices in column D. Column E and F auto increase off of D so I do not need to do anything to those columns. ... Example.xlsx
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.
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.
Using excel 2010: I am calculating a dollar weighting with one isolation factor, the state where the sale took place. My current formula is below. Each dollar amount in AG3:AG2000 has a corresponding weighting in AB3:AB2000. The results are isolated by the corresponding state in the range E3:E2000.
I want to add in an additional isolation, the month when the sale took place. The months are located in H3:H2000. Where in my current formula would I insert the isolation for the month?
=SUMPRODUCT(--(Sales!$E$3:$E$2000='2013 NB'!S8),
Sales!$AG$3:$AG$2000,Sales!$AB$3:$AB$2000)/
SUMIF(Sales!$E$3:$E$2000,'2013 NB'!S8,Sales!$AG$3:$AG$2000)
I need to gather total dollar amounts from a column based on their date/month entered.
I have a sample attached, in cell L2 for example I would need to gather all the dollar amounts in Column G that were entered for that month in column B that correspond to the month in column K.
I need to prorate a total dollar amount by a selected number of attendees. How can I do that with code, I know the formula for excel, but how do I put that on my user form so it only does the math as a new entry is submitted? I do not want to have the formula on the spreadsheet which is my report unless there is an entry on the row, which will only be the case when a user completes the user form. I just do not want the div/# error on the sheet visible if at all possible.
View 2 Replies View RelatedI've been trying to figure out a dollar weighted average formula in excel.
See attached file...
In sheet 1, I need to pull data from sheet 2 using a sumproduct formula to find the dollar weighted average maturity of a bond portfolio. Basically, I need to know how many days the portfolio has left to mature according to the portfolio's weighting by the amount in column H.
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.
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?
I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.
been trying to figure out a macro so when you press the update button it then just updates the monthly total.
Colac Production.xls
I run a rather simple loop (see below). But as it runs through A1 to A28000 it takes a lot of time. Is there a more efficient loop to operate this simple task?
View 9 Replies View RelatedI got individual dates in Column A. The format is 01/04/2012, 02/04/2012, 03/04/2012 and so on.
The person enters sales everyday. Now I want to make a new sheet which should sum up the sales month wise.
So in the new sheet I will have
Column A- Products
Column B- Sum of days in April 2012
Column C- Sum of Days in May 2012
Column D- Sum of Days June 2012
And so on
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.
I need a formula that will tell me how many minutes/hours are needed to hit a certain percentage.
For example, I need to do 60% of my time doing one thing (Team), and 40% of my time doing another (Individual). So, I need to know, if I've spent 5.5 hours doing Team, and 4 hours doing Individual, I need to know that I need to do .5 hours to hit the correct split.
I can't figure out what formula to use that will increase the current total to best figure out how many minutes I'll need to do either Team or Individual to hit the 60/40 split. It doesn't need to actually tell me 15 minutes, etc, but .25 would be fine (everything is done in .25 increments).
I have a column of names, and I want to be able to count all the instances of each name, as each instance represents a sale of a product.
Countif(Sales!B:B,"Dave") works, counting all the instances of Dave.
But if I have all the names in column A, and try to have column B give the results (from another WS), as in: =COUNTIF(Sales!B:B,'Best Customers'!A1), I get a "0" as the result. Yet XL help says countif can be used as =COUNTIF(A2:A5,A4). where A4 holds the value to search for.
While we are exploring this, is there a good way to look in a column, get every different instance of the names, and output them into another column?
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 RelatedI 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....
I need a formula to determine the average number of days between sales transactions for each item in inventory. There are several hundred different item numbers with thousands of transactions.
I have attached an example of what I'm looking for. I've toyed around with Average and If functions but can't seem to get it right.
Avg Number of Days - Excel Help.xlsx‎
In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
I have been a long time lurker of MrExcel, but have always been able to find the answer to my question within the forums. However, I am stumped. I am trying to create a pivot table that will show incremental goals by week, when I have a set goal for the month already. However, I don't want to do a straight average across all the weeks in a month. I would like to see it continually grow during the month. How can i mathmatically do that in Excel?
For example:
Jim had 191 sales leads in the last week of the month in December. Of those 191, he closed 29 of them, for a 15.2% close rate. I want to see him raise that by 6% by next December, so (6%/12 (# months) = .5%). He will need to increase his close rate to 15.7% by the end of January. So, for the month of January, we expect 955 sales leads (5*191) and 149.935 (15.7% of 955) closed. However, I don't want to say Week 1, 2, 3, 4, and 5 in January should all have 29.987 closes, I would like to be able to say week 1 = 25, week 2 = 27 week 3 = 30 week 4 = 32 and week 5 = 34, but have that incremental increase to total 149.935.
There is (sadly) no changing the percents, or the closed leads number. These have already been submitted and aproved. I just have to be able to let people see the goals by week now, but if they want to see the monthly goal, it must equal what has been approved.
I just can't figure out how to create a formula to figure out the average Percentage increase over two figures.
For example:
Amount 1 Amount 2
$17,330,000$20,150,000
Would I use the average function?
In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.
I don't know if it is possible or not and am working on a pre-existing worksheet.
Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.
How can I create a report to show the TOP 20% based on sales to grand total.
Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?
Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.
I am needing to create a table where one of the values is the mean sales from the last 3 months. Each month is in a different workbook. Each workbook is labeled "1 - Profit & Loss" where 1 is the month up to "12 - Profit & Loss". So each new workbook I create (save as and rename the month) I need it to update the workbooks it looks at.
I also am needed in another cell of the table the same month of the current workbook but exactly one year prior.
I am not at a full year yet, so until there are exactly 13 workbooks (where the 13'th can look back at the 1'st) the second formula should just display "No Comp"