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....
A column = contains dates but does not always have 5 days in a week. Holidays are not entered.
B column = price data for each day
All I want to do is get the highest price from the previous week. So for example last week highest price was 5000 then column C will display 5000 for this entire week. I tried using WEEKNUM and WEEKDAY but i am clueless on what to do after that. I'm trying to avoid macros or VB since im not that advance with that. But if I have to I will.
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.
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.
I have a query regarding making a Excel Sheet for Daily sales.
here I go, Well i want to make an Excel Sheet where in I just need to enter the Date, Invoice Number , Product , No of Product and rest it should calculate the VAT (Rounding Off) amount N den the Grand Total..
I am trying to build a sales sheet that will keep all the records during the month but every day it refreshes to a blank sheet, then allowing users to flick back to a previous day and all the data from then is still saved?
I think this is possible in excel as I am sure I have seen a working sheet before but I don't know where to start or even what the process would be called in excel?
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
I've a spreadsheet (see attached) which shows sales of 10 different fruits. I've used a LARGE formula (Cells O15:O17) to display the three highest sales and an OFFSET formula (Cells B15:B17) to show what the are.
I only ever want to show the top three sales but I need to take into account instances where more than one fruit has the same sales figure (for example, if lemons and pears both had sales figures of 27, they'd have to be shown along with grapes and strawberries).
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
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.
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'm working from a sales ledger file whereby I want all invoice data in one row although the Item Number & Item Description are on the first row. Rows 2-10 (for example) are the actual details of the invoice: Invoice #; Date; Customer ID; Customer Name; QTY; etc.
Is there a formula or easier way than copying/pasting the "Item Description" to the first column (J in this case) without any related invoice data?
The data was taken in 15 min intervals and is organized by date. I have one column with the date and time and another column with the data. I need to find the average for each day. I have almost a years worth of data. Is there any formula I can enter to find the values in a given day and return the average of the values (without having to select the data for each day)? I want to be able to copy the formula down a column with the value per day.
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 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.
Charter default premium is a calculated as a rate on outstanding charter hire.
Therefore if charter period is 1 year and daily hire is 20,000, outstanding charter hire (policy limit) at inception would be 20,000 x 365 = $7,300,000
However, by day 365 outstanding charter hire and prevailing policy limit would only be 20,000 x 1 day = $20,000
The premium is calculated on a daily pro rata basis on the reducing outstanding amounts.
I've calculated the premium on the attached spreadsheet assuming hire is monthly not daily, but it would be too laborious to try and calculate that for 12 months or more on a daily basis. What I'm hoping is that there's a formula, covering the range of data, presumably using the calculation on the first day and the last day, but which cuts out all the intermediate steps.
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
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.
Column A = Date Column B = Numbers Column C = results
As you can see Column C, I manually did what I want the formula to do, which is get last number from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays).
I create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)
In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.
My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0). I will manually make adjustment for February where 28 or 29 days applicable.
I am looking for a way to find a highest (and/or) lowest value in a list of numbers. The list keeps getting longer and after a certain point I will need the 2 highest (or lowest), then the 3 highest (or lowest). I know MIN and MAX will find the high and low, but how do I find the 2nd highest, etc?
I have a column of one to three digit numbers (I5:I34), some of which contain an asterisk after them. In I35 i want a formula to find the highest value from that column. Additionally, if two numbers in the column are the same value, but one contains an asterisk after it, it should use that value in I35, instead of the one without the asterisk.
At my work we use excel to keep a large number of labels done in one day, those labels are then added up to give our weeks total, running total and average by day. What i am looking to do though on a seperate sheet though is find out which day we had the most, an example of what it looks like
I am using VLOOKUP with the not_exact_match set to True, however instead of finding the next largest value that is less than value, I want to find a way of returning the next largest value that is greater than value. I have looked at using MATCH and OFFSET to try and increment the returned value by 1.
I have a list that kind of looks like this. I sorted it by date, then by location, and then by time. I need to find the earliest item in one location on a given day and display as a list.
Cells L5 - L65 have percentages. B5 - B65 Have the associates names. I want to write something in another cell that looks for the highest percentage and then prints the name as the highest.
I have a column that updates automatically by placing a value in in the next blank cell in the column. I am trying to write two formulas to identify the highest and lowest values in the preceding 10 cells of each update in the column but seem to be stuck in MIN/MAX/OFFSET hell.
