Using Lists Function Calculate The Outstanding Work Estimation Based On Available Information
Feb 10, 2009
I am trying to create a worksheet which can calculate the Outstanding Work Estimation based on available information.
The information available with me is presented in Rows 3, 4, 5, and 6. For these types of work orders, I have information regarding their Previous Outstanding Status, New Orders, Completed Orders, and at presented Unworked Orders (or Outstanding orders at the end of Jan 2008).
Second set of information is available in rows 9 to 23. This information provides for which type of orders were completed by Staff 1 and Staff 2.
Based on these two sets of information, I am trying to get to the point where I can calculate the following:
a) Applying the weight of orders (namely 1A, 1B, 1C and Blend) by using the drop down list function in column B, I should be able to get the results in Column C (C25 onwards till C40), Column D (D25 onwards till D40), and Column E (E25 onwards till E40). Simlarly for all other months...for February, March, and so on...till December 08.
View 2 Replies
ADVERTISEMENT
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Jul 21, 2014
I have a requirements to capture the data and calculate the qty based on the defined work week. How to to this in vba macro or a formulas. I'll giving a sample data as reference. In my sample data i have already the formulas
but i wanted to automate the work week calculation specially if the number of days in a month has been change.
Btw, the values of every column is came from other worksheet. i copy paste this data as my sample.
For ex : Today is July and it has 31 days, the 31 days will be distributed to the defined workweek and calculate the contents of the corresponding column.
here is the distribution of columns per week as reference.
(31 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day24 (8)
wk4 - Day25 to day 31 (7) columns
(30 days)
wk1 - Day1 to Day8 (8 colums)
wk2 - Day9 to Day16 (8 colums)
wk3 - Day17 to Day23 (7)
wk4 - Day23 to day 30 (7) columns
View 1 Replies
View Related
Mar 3, 2009
In a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
View 9 Replies
View Related
Apr 11, 2008
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
View 8 Replies
View Related
Mar 29, 2007
I have 2 lists in excel format, each list contains numbers. I need to identify which numbers are on one list that are not on the other list.
Is there a formula (hopefully easy) that I can use to identify these?
View 8 Replies
View Related
Nov 20, 2008
I have a sheet with many columns of data relating to different companies as shown below (obviously my actual sheet has much more data):
NOTE: the Mr. Excel forums display the data in the correct form. I've tried to fix it here but obviously that didn't work perfectly, so if you want to view it correctly, click on the link above.
Comp Name Facility Name Product Name clean date
Comp A
Plant 1A Prod A1 11/11/2008
Prod A2
Prod A3
Prod A4
Plant 2A Prod A1000 12/1/2008
Prod A1001
Comp B
Plant 1B Prod B1 12/5/2008
Prod B2
In a new worksheet I want to see only the information relating to the Facilities, specifically the clean date of each facility, such that the information looks something like this:
Facility Name clean date
Plant 1A 11/11/2008
Plant 2A 12/1/2008
Plant 1B 12/5/2008
I know that I can use a simply vlookup formula such as the one below:
=VLOOKUP(C21,Info,3,FALSE), where C21 is the Facility Name and "info" is a list of the last 3 columns of my initial datasheet. However, I need to remember add each new facility name to the 2nd list when it is added to the main datasheet.
Is there a way to have the 2nd sheet automatically add the new facility name every time I add one on the main sheet, but without showing the many empty rows inbetween?
View 4 Replies
View Related
Aug 17, 2009
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?
Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.
View 4 Replies
View Related
Apr 29, 2009
ive had to retype this so please bear with me. ive utilised the resources of this site before but ive come to the time where i need to ask a question of my own.
now i will refer to my attached document as what im trying to achieve.
my project is to create membership cards for our social club at work, out of a list of people we have on our manifest.
If you refer to column A, this is where i will dump the manifest of members as time progresses (keeping in mind that more names will be added to this list as it goes).
What i am hoping to achieve, is to start at Cell A2 (highlighted in yellow). For the example the membership name is "a". i want to hit the macro button and the following will occur.
1. data "a" from Cell A2 will be copied to B2 (highlighted in green) and G6 (highlighted in blue). This moves the name from being on the unregistered list over to a membership card and to the registered list, which allows me to cross check that im not doubling up on names with new applicants and current members.
2. once this occurs, i was cell A2 to delete and cell A3 to be moved up, so now, cell A3's data "b" (highlighted in orange) will be moved to A2.
3. a similar process as point 1 will now occur, where A2 will be moved to the highest empty cell in Column B, which would be B3 now. It will also copy to G18 into the 2nd membership card.
4. this process occurs until column A is empty in A2.
5. i wish to really only have an A4 page worth of membership cards before i print, so i wish to be able to "reset" the membership card boxes, so that instead of the membership cards continuing all the way down the worksheet, it will do say 12, then print, then start back in the 1st box. Ideally this is a loop so that it occurs until there are no more names in Column A.
This is a huge feet for me as an idea because ive never gone beyond using basic recorded macros, however im the type who just HAS to learn how to do stuff, so i will lap up any information or assistance i can recieve from anyone here.
View 11 Replies
View Related
Jan 29, 2013
I would like to Use two drop down list with two set of information to automatically update fields.
I have Job Titles (1-6) and Step (1-5). Each Job Title has a new pay rate and each step is an increase in pay. What I am trying to do is set up a drop down list where some one can drop down Job Title and step and have the rate automatically fill in.
I am able to get the Job title to auto fill the rate field, but am having trouble incorporating the step into the formula.
Here is what i currently have:
=VLOOKUP(H6,'Pay Rate'!$B$2:$G$12,3,FALSE)
H6 = Job tilte field on 'WorkSheet' (Sheet1)
'Pay Rate'!$B$2:$G$12 = Sheet2 and Step increases per Job Title
3 = the current column I was working with in order to make the rate appear
False = for exact info
I do not want to combine the two columns but keep them separate drop down list.
View 4 Replies
View Related
Jul 25, 2014
I have added 3 tables data .. also I had created a sample solution calc for emp1 and project1 ... I need to calculation the ratio between the months based on the working days and allocate the efforts accordingly.
View 2 Replies
View Related
Nov 11, 2013
I'm trying to create a database where when 3 dependent drop down list selections are combined, information is displayed.
I've tried VLOOKUP and INDEX/MATCH but to no avail.
Attached is the spreadsheet and what I am trying to accomplish. On the first sheet ("budget form") is a table named "Event Costs - room hire". I have created three drop down lists that interact based on what you select. Some rooms offer different services and so are dependent on the selection.
However, I want the final box to display the price depending on what is selected in the previous 3 lists. The amounts and a table I have created is in the "Product Database" sheet and the table is labelled, "Room Hire Control C".
View 5 Replies
View Related
Dec 3, 2013
calculating the percentage for outstanding reports.
My employees have 30 days to complete a report so if they complete the report within 30 days it is 100%
Each day the report is outstanding the % is reduced i.e. 32 days = 98%
I also have dates set-up to give me the actual # of days the report is outstanding i.e. (date received by me) - (date of the meeting) 12/1/13 - 11/30/13= 30, but the % is not working well
What I have tried for the % is
number of days outstanding /100 * 30 (number of days per month)
91/100*30
The % is needed for yearly evaluations
View 5 Replies
View Related
Jan 14, 2010
I'm trying to develop a formula that will take info from one tab (which would be the banks Cleared and Outstanding Checks report, and match with the checks that I have issued from the other tab. Basically this is for reconciling a bank account with the books. I want to add a column in tab #2 that shows if a check is cleared or Outstanding. So if I'm explaining this poorly, I apologize. Reply back if you have any specifics.
View 4 Replies
View Related
Jun 1, 2007
I am trying to write some VBA code for Excel that looks at a several different cells for a specific value and if that value is there returns a prompt message outlining to the user specific cell info. For instance, if some Excel cells in a column show that someone has not paid their bill yet, I would like a message to appear as soon as one opens the spreadsheet showing the bills for what customers are still unpaid.
View 4 Replies
View Related
Apr 11, 2013
Just like if I put the employee's number and work time for each day,
The excel will accumulate the hours automatically somewhere in the sheet. (I don't want the total hour cover each day's work time)
Is there anyway I can do it?
View 5 Replies
View Related
Sep 14, 2006
i have a few fields with dates in my excel sheet. I also have a field, where i want to automatically insert the business day between two other date-fields! Business date means days without the weekend and public holiday! Is it possible to do that? how?
View 9 Replies
View Related
Mar 10, 2014
i have a property and tenants have arrears in rental When i receive money from tenants i want excel to deduct that amount from arrears column first then balance from current rent.
View 4 Replies
View Related
Apr 15, 2014
I have to calculate employees work hours for overtime.
in the timesheets:
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 13:00 <----- total for cell D2,D3, and D4 is more than 40
A B C D E
1 ID Date Code Hours PayMethod
2 A123 4/14/14 TRN 20:00 Regular
3 A123 4/14/14 TTT 15:00 Regular
4 A123 4/14/14 TRN 5:00 Regular <----- to make 40
5 A123 4/14/14 TRT 8:00 Overtime <----- 13-5=8, so i have to write down 8 here for overtime
How can I do this?
I want to make a command button for macro to perform this.
View 2 Replies
View Related
Apr 23, 2009
Iam studying civil engineering and iam trying to make an excel sheet to calculate reinforcement required for solid slabs
to calculate the reinforcment first you should calculate positive and negative moments along short and long directions with dead and live loads
what iam trying to do is creating 2 lists the first list is to choose the RATIO of Short Span(a) divided by Long Span(b)
The Second List is to choose a case out of 9 cases of possible connections
the idea is to choose a ratio from list 1 and a case from list 2 to return the values of crossponding moments
The table is attached
View 2 Replies
View Related
Aug 2, 2014
I have been playing around with some data and can't seem to get it the way I want it. I have played around with Pivot tables and grouping but I can't seem to figure out how to accomplish what I need in Excel. To better explain I have attached some test data of what I am trying to accomplish.
View 14 Replies
View Related
Nov 11, 2008
I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).
I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.
View 3 Replies
View Related
Apr 2, 2014
I am trying to come up with a way, and do not know if it is possible. In the attached spreadsheet, I am looking to find a way that will automatically filter a group of records from the f column (all CC, all 2ndR, all NA) then calculate the information in in Columns G and H and place it in the appropriate box in D-21 through D 37. Example. I want all the rows that ONLY have CC in Column F. Then take the information from those records in Column G and column H. I want the sum of Column G in D21/2 and Column H in D23/4. I
View 2 Replies
View Related
Mar 8, 2013
I need also to calculate difference between dates(dd-mm-aaaa hh:mm) in workhours ( hh:mm):
The work period is 9-18 with lunch interval 13-14 The startdate and end date could be out of the work hours and i can't include the extra hours. I can have several days (workdays) at the difference, but i should maintain the format hh:mm.
Ex1:
Startdate 05-03-2013 18:34 ( date to calculation should be 05-03-2013 18:00)
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
will be equal to:
Startdate 06-03-2013 8:34 ( date to calculation sould be 06-03-2013 9:00 )
end date 06-03-2013 10:30
Time Difference 1:30 ( from 9 to 10:30 of 06-03-2013 )
Ex2:
Startdate 06-03-2013 12:01
End date 06-03-2013 14:28
Time Difference 01:29
View 3 Replies
View Related
Dec 27, 2013
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
View 3 Replies
View Related
Feb 28, 2013
I can calculate total hours when a user enters a start date/time and a finish date/time. The kick is I only want to include hours from 2:00 PM to 12:00 AM (10 hour period). So assuming all the start and end times will be in this range, how can i calculate work hours over multiple days? For example: Start date/time = 2/26/13 2:30 PM and end date/time = 2/28/13 10:30 PM. I want my calculated hours to show 28 hours.
View 1 Replies
View Related
Aug 21, 2008
I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View 2 Replies
View Related
Sep 23, 2009
I've got an IF statement that's based around what's chosen from a drop-down list. The problem is I'm using "" to stand for nothing being chosen in the cell in question, and this is returning an error message and highlighting the '""' (that looks confusing).
I've tried checking and unchecking 'ignore blanks' in data validation but nothing much has happened.
Here's an example of the formula I'm using:
=IF((AND(C40=A,D40="")),C9,(IF((OR(C40=A,D40=DA)),C9/2,"")))
The problematic "" is the first one.
View 7 Replies
View Related
Jan 1, 2010
I am trying to write a program that will allow a user to test their perception of time. Effectively, what I want to happen, is for a user to be able to press one command button, which will start the timer, and then the second when they think that 5 seconds have passed. The program then needs to record the actual time passed in a cell. Unfortunately, when I tried to write the program, it started a perpetual loop, as the user could not press the second button while the program was running.
View 5 Replies
View Related
Jul 16, 2008
Title should read "Data Validation Lists"...oops
I'm trying to format a spreadsheet so that based on the value in Column A, Coulmn B displays one of two spearate dropdown menus.
I know the following formula will not work, but that is the effect I want in Column B.
View 7 Replies
View Related
