Excel 2010 :: Summing Order Revenues Over Next Consecutive Calendar Days
May 7, 2012
I am using Excel 2010. I am tracking client orders on a spreadsheet. I would like to offer a 60 percent rebate on revenue from orders placed between the first order date and 30 calendar days thereafter. On day 31 and continuing through day 60, I would like to offer a 40% rebate. On orders placed on day 61 through 90, I would like to offer a rebate of 20%. From all of the orders placed by clients who start ordering on different dates, I need to sum their order revenue in these three time periods. I have the individual client order data arrayed on my spreadsheet - one order per row.
I am capturing the unique client ID number (Column A), the order date (Column B), and the associated revenue (Column C). Keying off of their unique ID number, I would like to calculate the sum of how much revenue is generated in calendar days 1 through 30, days 31 through 60, and days 61 through 90 from the very first order date from any given client. The start date can be any date in the month, so I can not rely on any calculation that uses the calendar month. Orders are not necessarily placed every day. Multiple orders may be placed on any given day, and then no orders may follow for many days afterward.
View 1 Replies
ADVERTISEMENT
Jun 15, 2010
how to display Sparkline from non-consecutive cells.
I want to display it from two apart ranges: A1:C1 and F1: J1
If it can't be done within the worksheet - is there a VBA code to workaround - such as filling-in an Array from the non-consecutive and using that Array as the Sparkline source ?
View 4 Replies
View Related
Dec 4, 2013
1.jpg
I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)
View 5 Replies
View Related
May 27, 2014
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
View 9 Replies
View Related
Jul 6, 2012
Using excel 2010, how do you insert a drop down calendar? Will this be able to display the date and then can I add a nuber to it and the result be a date
View 1 Replies
View Related
Jun 22, 2012
I have a vba script with forms that use the calendar object no longer supported in Office 2010, so I found a suitable substitute and replaced every instance of the calendar I could find across all the forms with the new solution. All seemed to work great. But when I closed the application and then reopened it, it threw an error saying there was a missing library.
When I went to the references it showed me that the Calendar object was missing. I have scoured the application for every possible instance and eliminated it, but apparently there is one or more still out there lurking in the weeds somewhere. Is there someway to search for the control that is tripping this error?
View 1 Replies
View Related
May 2, 2014
I am currently using a pop up calendar in Excel 2010 that with CTL+SHIFT+B that a calendar pops up, you select your date, and then the calendar closes. What I now need is to write a code or formula or something that when I click in any cell in column F lets say that the calendar opens automatically in that cell and then still close automatically after the date selection is made.
View 3 Replies
View Related
Nov 13, 2012
Is there a way to sort out the sheets into alphabetical order in excel 2010?
View 5 Replies
View Related
Apr 26, 2014
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
View 2 Replies
View Related
Apr 9, 2014
I'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?
View 3 Replies
View Related
Dec 29, 2012
I am trying to sort multiple values in ascending order (example attached) however I can't get this to work. Tried looking at a few different forums and although there is plenty of sorting questions out there they all tend to be relating to dates not different values (i.e text and numbers).
View 14 Replies
View Related
Oct 25, 2011
Find the sorting method of dates in excel 2010. I have dates in one column and i want to sort it, but I am not able to sort in ascending or descending order.
View 1 Replies
View Related
Jul 1, 2013
I wanna sort these numbers in descending order and related to each others in excel 2010.
H
G
F
E
D
C
B
A
1383.99
1533.954
831.197
1533.954
1533.954
1383.99
700
1533.954
1
[Code] .........
About descending and related to each others, I mean for example : #700 in column A,B,C,D,E,F,G,H places in the same row and the same happen to #1533.954 and others. About the numbers that are The One ( like 549.894), I need to put them in its column and a new row with empty cells in its other columns. In the end i want a table like this:
H
G
F
E
D
C
B
A
1533.954
1533.954
1533.954
[Code] .........
View 5 Replies
View Related
May 1, 2014
Have developed a form for my associates and want them to be able to move to the cellls I want them to fill data in to by merely hitting the TAB or ENTER key vs. having to move the cursor to each cell requiring them to input data.
Want cursor to move in a specific cell order. You would think you could merely say 1M, 2C, etc.
View 9 Replies
View Related
Nov 9, 2012
I have a spreadsheet with 6 months of data in one row. the dates are from A3 (Jan 1) to FY3 (Jun 30). The data is in range A4:FY4. The data is a "$" (without quotes). The $'s are entered randomly during the 6 month period. I need a formula to determine the days between the last occurrence and the next to last occurrence of the $. Example spreadsheet shows the $'s on Mar 28 and May 5 as the last two. The spreadsheet is attached. I am using Microsoft Office Excel 2010.
View 6 Replies
View Related
Apr 8, 2013
I am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
View 12 Replies
View Related
Jan 5, 2013
Working on Excel 2010
Need a formula to calculate the number of leave days.
C3 - Start Date, e.g. 01/07/2011
D3 - Calculation date, e.g. 31/12/2012
E3 - Number of months - ??????? Forumal need from 01/07/2011 to 31/12/2012
F3 - Days per month, e.g. 1.25
G3 - E3*F3
View 6 Replies
View Related
Jul 5, 2012
Using Excel 2010.
I am trying to come up with a formula that will return a total average from two columns of dates with criteria. The range will need to cover an entire column as my data is continuously growing and the criteria would have to limit the start date to each month. I have tried
=AVERAGEIFS(DAYS360(A:A,B:B),A:A,">5/1/2012",A:A,"5/1/2012",A:A,"
View 2 Replies
View Related
Feb 26, 2014
I need conditional format in column Q which highlight in red, any date over 14 days old.
This column also contains texts (non-dates) and these need to be left alone.
I've experimented with a few bits of formula I've found online, but nothing has worked . .
I'm on Excel 2010.
View 3 Replies
View Related
Jan 23, 2014
I have to dig out data for all employees first entry and last exit for two months. My raw data is in sheet 1 as under:
LAST_NAME
FIRST_NAME
EMPLOYEE
[Code]...
I am using excel 2010
View 8 Replies
View Related
Jan 2, 2014
I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.
=SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13").
I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.
View 13 Replies
View Related
Jan 20, 2012
Excel 2010 Higher than past average formula?
Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..
View 3 Replies
View Related
Feb 13, 2013
Im having a hard time creating the formula to calculate the min number of employees to complete a number of jobs that come in each day and need to be finished in 18 days or less.
Data that I have:
Forecast of the number of jobs for 251 days ( jobs range from 10 -80 per day) Each job needs to be finished in 18 days or less we need to staff a fixed number of employees for the whole year each employee can complete 4 jobs per day no weekends
i have excel 2010 and crystal ball
View 1 Replies
View Related
Aug 1, 2013
I'm having trouble defining the time overlaps on continuous days for the varying results of the runs for my simulation.
For the following data set, without counting overlaps there are 18 total spare requirements, but if I take into account their overlaps in their duration there should be a total of 11 per day. For example in row 4 this event overlapped in the same day with the event of row 5 so both had only 2 overlaps as a result (So there is not possible to be 1 as an answer for overlap, there is only from 2 to "n" amount or 0).
But if an event overlaps with more than one in their duration the result would be their maximum overlap during this time frame.
So then I have for each event an Starting date, starting time, duration, ending date and ending duration.And for each day I would need the total amount of overlaps occurred for the duration of each event and from those I would take their maximum amount of overlaps occurred per day. To obtain their real requirement as shown on the file.
The manual procedure I'm using looks like this for the first day: (Graphical representation)
Event 1 occurs from 17:02 to 20:29 and only overlaps with Event 3, so it's 2
Event 2 occurs from 20:38 to 23:38, also only overlaps with Event 3, then its also a 2
For Event 3 in its whole duration only overlapped with Even 1 and Event 2, one at a time so its maximum overlaps where only 2.
And that's what I'm using so far to determine these amounts but since is a simulation, I would end up with several data from each run and this method would be too long. That's why I would need this template to paste data for each run (15 days) and obtain my results faster.
View 1 Replies
View Related
Jan 12, 2014
I am trying to do a column full of Month,Year dates in consecutive order (i.e. Jan 14, Feb 14, Mar 14, Apr 14, May 14.)
View 1 Replies
View Related
Jun 9, 2009
formula to identify consecutive numbers in order, but having trouble figuring out how to identify consecutive numbers in random order.
Cell M1,N1,O1,P1, and Q1 each have a number, 1,4,9,3 and 7.
We have 3 and 4 being consecutive number but they are not in order, would like help in a formula to put a 1 on an empty cell S indicating that there is a consecutive number with a 1 if there are no consecutive numbers then it would give a 0.
The current formula only works if the consecutive numbers are in order, 1-2, 3-4, 5-6, etc...
=IF(SUMPRODUCT(--(N1:Q1-M1:P1=1)),1,0)
View 9 Replies
View Related
Jun 27, 2006
I am tracking attendance of individuals in an Excel spreadsheet. I would like to determine how many consecutive days each individual has attended. The spreadsheet has two pertinent columns which contain the individual's name and the date of attendance. How can I determine the number of consecutive days of attendance?
View 5 Replies
View Related
Mar 20, 2014
Excel 2010 - I need to create a graph that shows a week's use of a sportsground showing what sports were played, on which days and between which times. I thought I could show the times on the vertical axis, days on the horizontal axis and then the sports played in those times, however I'm unsure as to how to get the graph to recognise the times.
I've attached an example of some data and how I'd like to see it, I'm just not sure how I can do this using the chart set up!
Attached File : Graphs.xlsx
View 10 Replies
View Related
Apr 25, 2014
I'm in Excel 2010, and the cell with the date I want to work from is H22.
I'm trying to get the difference of the (date+12 months)-TODAY() to appear in months and days.
Here's the latest thing I tried (that doesn't work):
=IF(DATEDIF(H22,TODAY(),"y")>=1,DATEDIF(H22,TODAY(),"y")&" yrs, "&DATEDIF(H22,TODAY(),"ym")&" mths,
"&DATEDIF(H22,TODAY(),"md")&" days",IF(DATEDIF(H22,TODAY(),"ym")>=1,DATEDIF(H22,TODAY(),"ym")&" mths, "&DATEDIF(H22,TODAY(),"md")&" days",DATEDIF(H22,TODAY(),"md")&" days"))
I should also probably note that the date in H22 is the result of another function.
=EDATE(G22,12)
View 5 Replies
View Related
Oct 27, 2012
Need to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.
Score1
Serial #
Rank
90
146168
1
[Code] .....
View 5 Replies
View Related