I have a list of days and hours, and data for whether a type of event either did or did not occur for each sampled hour. I need to return a new value, some sort of "Event Day" value, which will essentially tell me if an event occurred at least once for each day.
Attached is a sheet with 4 days' worth of data. Jan 1st and 3rd 2006 are my two "Event Days".
To complicate things, I can't be sure I have 24 hour readings for each day, so I can't assume each day is a data range (e.g. A1:A24). Hours might be missing. (As such, I am trying to organise along the lines of IF the preceding cell is the same date...)
I also have lots of data stored as months, so would love a formula I could just autofill down. All it needs to do is effectively flag up for me when a day has experienced one event.
I have an excel work sheet with 2 columns and hundreds of rows. The first column is time in minutes over a number a years and the second is a list of corresponding data readings (numbers). I want to only select on the hour data from the list however I cannot seem to do it. The times vary in the sense that they are not all just 1 minute apart, some are every minute, then there might be a 10 minute gap in data, a few more minutes, then another gap etc.. All I want to be able to do is to select all the on the hour data from the list and its corresponding data value.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form: | Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
I have a large sheet with the values of power from a counter in a 15 minute base.
A B 01.01.09 00:15 0,25 01.01.09 00:30 1,15 01.01.09 00:45 0,75 01.01.09 01:00 2,01 and son on until the end of the month What I am trying to do is to create a macro which calculates the average per hour and put in another columns like this: 01.01.09 01:00 average from 00:15 until 01:00 01.01.09 02:00 average from 01:15 until 02:00 and so on until the end of the month.
I have a string of data containing hourly timestamps with a value attached to each timestamp. I need the timestamps to be every 15th minutes instead of hourly. So basically, I need the hourly value to be continued four times for each timestamp.
See the attachecd workbook. It should be quite simple, but for some reason I just cant figure it out!? A bit embarressed In the woorkbook, I need VGTs and VPTs to be in a 15 minutes granularity.
ERROR: Cannot upload workbook - uploader not showing so here are shot of the raw data:
I have a worksheet containing one timestamp column and a single column of data, the interval being one minute. How can I aggregate it to get half-hourly totals?
I have got a lot of data to put in a chart that updates every 5 minutes with new data - i have a chart that Line chart that shows this.
It shows the cost of something every 5 minutes.
I have put this in a chart using offset but obviously i want to show the cost over the course of the day - because this updates every 5 minutes, the axis at the bottom (which has the time of the data) is massive and doesn't look good.
Is there a way to show the axis at an hourly interval but keep all the data in the chart?
I have A:1 - X365 filled with hourly energy prices for 2013. I need to convert these to a single column - A1:A8760. I have tried writing some code and some macros with no success.
If I were to do it manually 365 times, the macro would look like this:
I have been using the attached formula to calculate total volume (customers seen) by hour (between 1 and 2, 2 and 3 and so on...). But how can I change this formula to take a census at a certain period of time (let's do it on the hour to be easy) instead of double-counting those who were there, but left in the same hour and those who just entered in the same hour. I hope this makes sense...a true census really should not count two people twice though which I think is happening here...
I hate to create a sense of urgency, but would love to begin working on this later tonight. Please let me know if you need more detail.
I am trying to create a conditional formula. I have multiple workers that work for different $$ per hour. I am keeping the hourly rates on one page for security purposes (the person that is going to be updating the hours worked is not very computer savy and they less they have to type the better) and the hours on another... I want copy a formula down the page that will calculate the hours to the hourly rate. The issue is that i want the formula to look at all of the hourly rates (assume they get raises) for Joe and multiply the most recent hourly rate times the hours... in addition I don't want the formula to re-calculate when a new hourly rate is put into the hours sheet.
Example
Jan. 1 joe works 8hours at $10 per hour formula in A1 (or wherever) calculates $80 Jan. 2 joe gets $1 raise Jan. 2 Joe works 8 hours at $11 per hour formual in A2 calculates $88 but formula in A1 maintains the $80 and does not calculate the $1 raise.
I am trying to build a costings sheet for shift work. The first problem I am experiencing is getting excel to split up a shift into the different bands.
For example the shift needs to be calculated on different rate bands, from 00:00 to 08:00, 08:00 to 19:00 and then 19:00 to 00:00. There is a different hourly rate for each band. Therefore if I enter a start time and end time, I need excel to calculate the total charge over the different rate bands.
For example a shift 07:00 - 21:00 would have:
1 hour 07:00 - 08:00 at Night rate 11 hours 08:00 to 19:00 at Day Rate 2 hours 19:00 to 21:00 at Night Rate
Once I have this worked out I have the added complication of different rates on Friday evening,s, Saturdays and Sundays etc. But at the moment one problem at a time !
I have a list of values at 'x' min intervals (the intervals sometime changes). I want to sum each hour's values eg. between 0:0:0 hour and 0:59:59, I want to add values 1040+800+860+870. Then I want to proceed and add all values for the next hour i.e. between 1:0:0 and 1:59:59, and so on. I have about 65000 of these calculations to do, so the need for some vba programming.
i have a s/sheet with thousands of times in a column (eg col1 below in 24 hour format) and would like these in an adjacent column in hourly intervals (eg in Col2 below
I 've used the site many times in the past, but this is my first post.
Above is a small example of the data Im working with. I'm not wanting to plot all of this out by hand, so hopefully someone can help (there are around 30,000 samples). I'm looking to track by hour when a customer is actual here. The first row is one customer in @ 00:22 and out the next day at 14:05.
I would like to have dates of the year down column A and hours 0 through 23 across row 1. Then a number of customer here on 1/1/2008 by every hour of the day on to 1/2/2008 so on and so.
I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open. This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4. Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run.
How can I get the median of a column that contains both hourly and annual rates? I would like to either multiply the hourly rate * 2080 or divide the annual rate by 2080 then get the median.
I am trying to calculate earnings based upon hours worked based on 24 hours format (time out - time in = time worked) multiplied by hourly rate. I cannot get the proper calculation. How can I attach a copy of the Excel worksheet?
I'm having trouble with conditionals based on a primarily a 4 hour minimum. I am trying to set it up so that anything under 4 is shown as the minimum 4 hours + Travel Time - Lunch Time or if it is greater than 4, shown as Regular Hours + Travel Time (since travel cannot be overtime) - Lunch Time & Overtime (anything over 8 hours). Additionally, I was wondering if it would be possible to incorporate an additional IF formula based on the date. If the entered date is on a weekend, then all hours worked will need to be overtime rather than regular.
I am trying to create a data entry sheet to enter quotes on. When a quote is received, I click on my "Add quote" button and a userform appears. Data is entered into the userform (frmEntryForm) and returned back to the next available row.
I also need to be able to:
Edit a row by double-clicking it. When a row is double-clicked, data from that row is passed back to the userform, edited and returned back to the same row (to prevent duplicates).Validate that all fields are complete within the userform where relevant (i.e. if the work is not complete or in progress then the "Invoice Number" and "Actual Cost" fields are disabled and blanked to prevent entry (I think this is almost sorted judging by my tests)
I have attached my sheet : 2014 Gatwick Quote Log (Macro Enabled).xlsm
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
I am trying to find the largest value, second largest, and third largest value for a column of numbers then return a content value for the adjacent column that contains the name for which each. The problem I am running into is when the second highest has the same value as the highest and also when the third value is zero (n=meaning there are only non-zero numbers for the column). I am using these formulas currently whic work when the numbers are not the same and I have atleast 3 non-zero numbers but I need to modify in order to account for the problem stated above...
For a landscaping company, who is looking to service gardens more effectively/efficiently, they are mirroring the below variables to the amount the client is willing to pay per hour. I also have a sample file.
I have, on Sheet1, 8 columns I'm working with:
R3:R132 is the currently hourly rate
L3:L132, M3:M132, N3:N132 are current units, volume, and labor hours respectively by location.
AD3:AD132, AE3:AE132, AF3:AF132 are new units, volume, and labor hours respectively by location.
AI3:AI132 is the suggested hourly rate by location. Note this is already calculated and filled in. I need to calculate new units, volume, and labor hours based on the suggested hourly rate.
On Sheet2, I have 4 columns I'm working with:
A2:A161, B2:B161, C2:C161, B2:B161 are hourly rates, units, volume, and labor hours respectively.
They are laid out in combination with one another to show possible combinations of these variables that make sense for the landscaping company.
My goal is to figure out what combination the company wants to go with for a given hourly rate on Sheet1 given the following logic to follow:
1) Attempt to reduce labor hours to get to suggested hourly rate - This is to calculate new labor hours on Sheet1 in cells AF3:AF132.
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00 columns E, F respectively have cust #B100 & 40.00 columns G,H respectively have cust #C100 & 60.00 Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
I am trying to come up with a formula that will give me the date in the cell right above the first cell that contains a value that is greater than zero.
I am trying to have the user click on a drop down list that shows several scenarios and return a score based on the scenarios. I created a list on a separate worksheet. The list contains the following:
Comprehensive list of policies 100% General policies with no details 75% Purchased policy templates 50% No policies 0%
I would like the user to see this list including the corresponding scores,but when they select one, I would like the percentage to be returned in the cell.
I tried using Data Validation with VLOOKUP and named my list; however, it returns the text in the first column instead of the percentage.
I have attached a sample file. What I need the formula to do is lookup the value in Column A on 'EE Data Sheet' (which has multiple occurances of the same value) and give me the corresponding value in Column E (ID #) only if the date in Column K (Service Dt) is greater than 7/21/2008. The return value needs to be on the '151_SD Sheet'.
In other words, I need the ID number from Column E on the 'EE Data Sheet' returned to me on Sheet 151_SD for each occurrance of 1806-151 (Column A on EE Data Sheet) only if the date in column K is greater than 7/21/2008. The areas highlighted in yellow show the data to be returned on the 151-SD sheet. I tried vlookup & match and hlookup & match, but I only got the first occurance of 1806-151.
i need to return the rows in another sheet that have values only. for example if cell "A1" = 1 "A2"=2 "A3" = nothing "A4"=4 i want cells "B1" "B2" "B3" to return 1,2,4 respectivley.
