After creating different variables for the different years, in this case: 1988, 1989, 1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, I want to be able to pull out the number of days that goes into those years as separate variables.
I recently manage to create a spreadsheet. On the spreadsheet what I am looking to do is once I change the year in cell U1 from 2010 to 2011 to automatically change the days and the date number, and where Sat and Sun preferably to auto-fill in yellow the whole column within the table as you can see in the spreadsheet.if not then just do not display Sat/Sun columns at all..
I have a start date dd/mm/yyyy, and require a formula that everytime a spreadsheet is opened, based on the current date, to calculate how many years, months and days have elasped yy mm dd since the start date...
I've got daily water level data that spans around one hundred years. I am looking for monthly average water level data, but rather than using the whole month, I'd like to average the first two days and the last two days. I want to have a single value for each month in the 100 years of data.
If the data was smaller I could do this by hand, however having 100 years worth of data = 1200 months = so many days...
It's tough since months don't have a set number of days, and then leap years and it all gets so complicated. I've never really had a problem where date was a criteria to pull values from, and I don't know how to work with this. The internet suggest the Averageif function which I have dabbled with, but no dice.
I am looking for a formula that will return the number of months (periods) between two dates, ignoring days and years. Using the above dates, which cannot be changed, the result needs to be 63. The formulas I have tried keep returning 64 because my later date is at the end of a period, and my earlier date is at the beginning.
I have a number of identical workbooks used for data capture within the organisation, and need to continue development as time goes on.
Whenever I make a change to the core Excel functions of the workbooks I simply use a VBA procedure to conduct this change within each of the production workbooks, however I am stumped as to how I can achieve this for changes to the VBA code.
I have made some changes to the code held within one of the modules, and have also modified the form slightly. What I want to be able to do is run a VBA procedure that either updates the production workbooks from a central source, or by importing previously exported code / forms.
I have a number of sheets that have sheet macros that are identical.... i am changing the sheet macro in 1 and want to see if there is an automatic way to distribute it to the other sheets automatically.
I am trying to come up with a formula that will distribute the numbers as shown in this diagram:
To get the value of each number divide 42,000 by the last number in the pattern. For example the fifth column from the left would be 42,000/18. That means that each number would have a value of 2,333.33. By the time you reach the half way mark, nine in that example, you should have around 21,000, and the other 21,000 should be distributed between the last half (block 10 thru 18). The formulas would have to work on all of the patterns. Can anyone think of a way to match the pattern shown? It doesn't have to be exactly the same pattern just as long as the number of blocks remains the same for each half, and they total 21,000 for each half without going over 42,000 all together. There has to be 11 rows from top to bottom.
I am using conditional formating to show dates as a bar graph (nice little template I got from Vertex 42). I have fee dollars in another column that I would like to distribute according to those dates. I have posted a snapshot of the image here:
how I can get the dollars to populate the cells according to the dates.
I have a master spreadsheet with names and addresses and action dates all in one sheet tab.
I want to be able to get all the data that pertains to action dates in March 08, for example, onto a sheet tab in the same spreadsheet called march 08, and then all the data from action dates in april 08 to go to a sheet tab called april 08 etc etc
I have a sheet with 5 columns in use at the moment. Col: A - E . Col C has a Score and Col D has Game #
I'll like some code to look at each cell in col D and based on the Game# copy the value from the corresponding Col C to the Game column that is set up for that Game. ( I can set these up manually )
Col D can contain both numbers or text
IF D2 = 23, C2 = H2 IF D2 = 29, C2 = M2 IF D2 = "HOME", C2 = W2
Col D can contain up to 20 possible numeric values & 8 possible text values
Also: Since I know all the 28 possible values that Col D can contain, how can I also get those columns set up automatically using VBA and SUM those columns that are set up with the numeric values at the last row.
I imported a CSV file into Excel, and the data from the CSV file all ligned up into one column. For example: I have (date, price) both in one column. I need to separate these values into two respective columns.
I'm working with a dataset from a survey that has recently taken place. The original structure of certain questions in the survey were multiple choice. The ideal structure for these questions, in terms of variables, is that each possible option for the question (A,B,C,D... etc) have a separate value, with a 1 for yes (reported) and 0 for no (not reported). In other words, if Question1 has multiple possible answers (A,B,C...), then there should not be one variable created from this question, but rather, there should be a variable for each possible response: Question1_A, Question1_B, Question1_C, and so on. And within each of these variables, should be a 1 or 0 depending on if that option was reported or not.
The format I received the data in, though, was a single variable with a list of letters, (e.g. B,D,F) for each option reported.
Variable: Question1 Row 1 A, B, C Row 2 B, E, F Row 3 B, D, E
The data I have right now is housed in Excel prior to exporting to SPSS and/or STATA. I need to find a way to distribute these values into separate columns (variables). The tricky part, is that if I simply use "text to columns", it distributes the letters to new columns, but doesn't take into account that the first value reported might not be A. Is there a way to take the list of values in the single columns, and distribute them to assigned newly created columns?
I have a series of data that acts upon a traffic light system, i.e. Green, Amber and Red. These variables are posted along row 1 for example and there are 10 columns. Per column I have a tick and cross to answer a question. How can I find out how many ticks were on green days, amber days and red days? I have attached an example.
We have a client that has to total the number of days that people received medical care (for insurance purposes). BUT...they can't count duplicate days. Some of the dates show a range of days, others only one day. They've asked for my help. (We're actually a computer services firm, but I'm pretty good with Excel...until now!)
In column C I have placed the correct totals for the date ranges, according to the payment policies. But I can't figure out how to write the formula that accommodates all the variables (mostly since all the unique dates aren't entered--only ranges).
Each line is a different medical charge, so line 1 might be 22 days of hospital stay. Line 2 is for a different charge, but covers the same dates, so it can't count. Lines 3, 4, and 5 all occur within the previously calculated range, so they don't count. The date range in line 6 is six days but 4 of them have been counted already, only 2 days fall outside an already calculated range.
It would be easy enough to do this manually for a small list, but this patient has 1367 entries in 4 months of care! And there are others that are much larger.
Fortunately, all the dates are in calendar order. Also, fortunately, they don't need line-by-line totals, just the grand total at the end of all the unique days in the range. So if I can figure out how many unique days are in the columns ....
I get given a csv file on a monthly basis which contains consumption data per day for the specified period. This sounds simple but on occasion (more often than not) the data has missing days. This can cause me problem later on in my analysis.
I can happily total the monthly consumption using the date and month text. What i want to do however is to sort the csv file into daily consumption and highlight the missing days i.e. have a range of the days in the month and allocate the daily data to the correct date. I currently do this manually but know that there must be a better, automated approach... searching for matching dates for example?
In my head i'm thinking the following approach but lack the coding skills to do it.
1. Define the start and end dates. Perhaps count the number of days between the two dates and autofill the start date down the appropriate number of days in column A?
2. Paste the csv file into a different sheet and, starting from the top, cut and paste the csv data to the correct date created in step 1. Do this for each row based on the csv data.