Formula To Show Project Size Based On Number Of Hours
I need to show the size of a project in a cell based on it's number of hours, e.g.
Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE
How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sum The Total Hours By Project Between Two Dates
I need to sum the total hours by project between two dates. There can be multiple projects and the two dates can vary. So... In column A are the project # (say A2 001, A3 001, A4 002, A5 003) In column B is Yes or No for each project (contract Signed?) In Column C through Z, row 1, are dates (shows the Monday of each week) In Column C through Z, row 2-5 (which corresponds to the projects 001, 001, 002, and 003 above) are the number of hours worked that week. On a separate tab (lets call it MonthTab) is the start and end date for each month (Jan through Dec) On a separate tab I want to SUM the hours by Month for each project (so for project 001 would need to go across multiple rows), that has a Yes in column two. Therefore the hours summed For January will be taken from MonthTab and be between StartJan and EndJan.
View Replies!
View Related
Formula To Calculate Pay Based On Hours Worked
i am trying to make an excel spreadsheet so i can keep track of my hours and pay at work... i know how to do mult and add but i wanted to know how to do the 2 together.... for example Hours ST OT Total Hrs Gross Pay 8 2 10 ? i would like help figuring out the gross pay if say for example my st time rated is 21.21 an hr, and the ot rate is 31.82 how can i get the 8 times 21.21 = 169.68 and the OT 2hrs times 63.64 to show up in the gross pay as a total of 233.32 - what would the formula look like?
View Replies!
View Related
Count Hours Between 2 Times Based On Hours In Another Cell
A1 is 10 (10 hrs worked) , A2 is 10:30am (in time), A3 is 9:00pm (out time), A4 needs to be the total hours and minutes between A2 and A3 based on the hours listed in A-1. What i need is a formula that will calculate the hours and minutes between the 2 times based on hours entered in A1 but that will also compensate for a manadatory 30 minute lunch that needs to be deducted from the total hours if hrs listed in A1 are more than 6. example: worked 10HRS, 10:30am to 9:00pm, Total hrs is 10hrs 30min, which should be just 10 since the lunch is a none work time and must be subtracted. If a person worked more than 6hrs, they must take a lunch. if they worked less, than 6 then they don't have to. I need a calcuation to recognize the greater than, less than factor into the equasion also.
View Replies!
View Related
Left & Right Formula To Show Number Not/as Well
I have numbers in a cell such as 1/25, 4/31, 12/35 etc I have the current formula to extract the left or right number but when there is only a single digit it also includes the / is there a way of eliminating this. The numbers vary from 1 to 2 digits. My current formula is =IF(C15<18,LEFT(E8,2))+IF(C15>18,RIGHT(E8,2)) I only want it to show the number not the forward slash
View Replies!
View Related
Date Range (formula Show A Week Number In The 2nd Table )
I have two tables: the 1st table consists of date range (From and To) and week number while the other table has only dates. Example: 1st Table FROM TO WK 3/27/2009 4/2/200914 4/3/2009 4/9/200915 4/10/2009 4/16/200916 4/17/2009 4/23/200917 4/24/2009 4/30/200918 2nd Table DATE 03/28/2009 04/11/2009 04/26/2009 Need simple formula that would show a wk number in the 2nd table (2nd column)? I.e 03/28/2009 has wk no. 14, etc.
View Replies!
View Related
Formula To Show The Value Of One Cell Based On The Value Of Another To Automate Some Internal Processes
I'm trying to get a formula to show me the value of one cell based on the value of another to automate some internal processes. Cell J3 returns today's date, then J4 says what week number that is. J5 concatenates the two to make the look up. Column A is named range "PN", and columns C onwards are named ranges to match the column heading. What I'm wanting is for the result in test cell 2 (J8), is to return the stock requirements for the current week, based on the result of cell J5. I can do this by using nested IF statements, but that would be 8 cells to calculate, then another one to find the cell that has a value. If there a way to get Excel to recognize the value in J5 as a range name, so it just needs the one formula?
View Replies!
View Related
Formula To Show A Date Range Based On The Amount Shown In Another Cell
Attached is a excel file that has a working formula for tracking cashier variances. I edited out names etc. I added a new cell called Track Back on the employee search sheet. What I want to do is only show variances for the amount of days back selected in the Track Back cell. For example if I select the last 30 days, only the last 30 days would show up below in the sheet. I am not sure if this is even possible based on the forumla that is already on the sheet. I couldn't figure out a way of doing it. But there are a lot of people on here much better with excel than me
View Replies!
View Related
Vba Formula Automatically Inserts Into The MS Project Box
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
View Replies!
View Related
Find Variable Size Number Within Text String
I have a spreadsheet with a couple of hundred rows on each tab that looks like this: A1: #1 PENROSE TIJUANA MAMA PCH 2620039190 665794 12.00 CT 1 Front 1 1 1. The only constant is the 10 digit number followed by the 6 digit number in the middle and I want to use it to parse the text string. I want the output to be everything prior to the 10 digit number in A1, the 10 digit number in B1, the 6 digit number in c1 and everything else in D1 and so on through the rows.
View Replies!
View Related
Firm Size Based On Employment
We have list of firms and employees for six years and we need to give size no 1,2 or 3 for each firm based on average number of employees as the following : size 1 for 1-9 employee size 2 for 10-999 employee size 3 for 1000-above
View Replies!
View Related
Update A Formula Based On A Newly Inputed Number?
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have B1 = 1000 B2 = 2000 B3 = 1400 B4 = ??? D2 = (B3-B2)/B2 is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
View Replies!
View Related
SUMIF Formula With Sum_Range Based On Column Index Number
Following is a summarized example of my data and what I am trying to accomplish. [Column A] contains a list of account numbers. [Column B] contains current balances, [column C] contains balances from one month ago, [column D] contains balances from two months ago. Within the same spreadsheet I want the ability to type in the account number in one cell and then the column number in another cell. For example, If I type in the account number 1234 and the column number 3, I would get the balance from [column c]...if I typed in the column number 4, I would get the balance from [column D]. My first thought was to use a simple SUMIF formula that would compare the account number I type with the account numbers found in [column A]. The problem is getting a formula that can translate the number 3 to [column C] or the number 4 to [column D]. Note: the actual spreadsheet I am using extends out to column BI. This is simuilar to the Column Index Number used in a VLOOKUP formula.
View Replies!
View Related
Show Text & Number With Number Format
I have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is $" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in $-600000000. I'd like it to look like $(600,000,000).
View Replies!
View Related
Set Window Size Based On Screen Resolution
I have an Excel based application that needs to run with a screen resolution of 1024*768 in order to display correctly. Is it possible to use VBA to detect the users screen resolution. Then: If the screen resolution is not set to 1024*768 then resize Excel's main window to run at that width and height?
View Replies!
View Related
Records The Total Number Of Hours
I'm trying to create a userform which calculates and records the total number of hours a user puts in, however it can not exceed 40 hrs. Dim intProject1 As Integer Dim intProject2 As Integer Dim intProject3 As Integer Dim intTotal As Integer intProject1 = txtProject1.Text intProject2 = txtProject2.Text intProject3 = txtProject3.Text intTotal = txtTotal.Text If txtTotal > 40 Then txtTotal = txtProject1.Text + _ txtProject2.Text txtProject3.Text txtTotal.Text = Format(txtTotal, "currency") Else MsgBox ["Total exceeds 40 hours."] End If If you guys could help me out I appreciate it Thanks End Sub Private Sub lblProject1_Click()
View Replies!
View Related
Round Number Of Hours Between 2 Times
I'm trying to do a simple calculation of the # of hours between 2 times, rounded up to the nearest hours. Somehow along the way I've ended up with this massive formula below, which is doing what it's supposed to do except for certain cases (like the one in the attached file...where it says the difference between 2am and 3am is 2 hours) =ROUNDUP(IF(B2="",0,IF(A2="",0,(IF(B2<A2,((((B2+1)-A2)*1440)/60),(((B2-A2)*1440)/60))))),0)
View Replies!
View Related
Average Formula When Sample Size Changes
I am computing the average of anywhere from 5-10 numbers. When the numbers are not inserted there is nothing left in the cell. The problem is I get a value that is close to the average but not the real average. (Also the cells I am using are N37:Q41 for the first 5 samples which is constant the next 5 are not always constant W37:Z41) Here is the formula I am using: =IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z38),IF((W40="")+(W41=""),AVERAGE(N37:Q41,W37:Z39),IF(W41="",AVERAGE(N37:Q41,W37:Z40),AVERAGE(N37:Q41,W37:Z41)))))) I have the same problem with a standard deviation formula and the same group of numbers (I get something close but a few points off). =IF((W37="")+(W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41),IF((W38="")+(W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37),IF((W39="")+(W40="")+(W41=""),STDEV(N37:Q41,W37:Z38),IF((W40="")+(W41=""),STDEV(N37:Q41,W37:Z39),IF(W41="",STDEV(N37:Q41,W37:Z40),STDEV(N37:Q41,W37:Z41))))))
View Replies!
View Related
Formula With Variable Range Size
It is useful to use lookup function to find the student name. However, if the table array size is variable (say, a new student enrolled and the table array will be $A$2:$B$7), the table array size will be changed and we need to change the vlookup function. How to avoid it? Can we use some kind of variable for the See attached file( book1)
View Replies!
View Related
Automatically Give Number Of Hours Worked
I'm working with a spreadsheet that works out how many hours someone has worked on a given shift. In cell C4 I have the formula =E4-D4+IF(D4>E4,1) In cells D4 & D5 I have an entered number in time format (custom hh:mm) The problem with this is that when a time is entered in either D4 or E4 it automatically gives me a number of hours worked (upto midnight) without any input in the other. Aim: Not to show the number of hours worked in cell C4 until hours in both cells D4 & E4 have been entered. NB. Sometimes the start times are 20:00 until 08:00 (night shift) hence answer 10:00.
View Replies!
View Related
Expressing A Number Into Days-hours-minutes
I'm hoping someone here might be able to help me please? I am trying to write a function that will convert a number into days-hours-minutes. I have managed to get as far as hours-minutes using the following function. =INT(A1/60)&"h "&ROUND(MOD(A1/60,1)*60,0)&"m" e.g. If A1 = "7090" the result of this function will be "118h 10m". I now need to express the result as xxxd xxxh xxxm and this is where I am stuck!
View Replies!
View Related
Formula That Will Determine The Number Of Days That Fall In A Specific Month Based On A Date Range
I'm needing a formula that will determine the number of days that fall in a specific month based on a date range. For example, if I have a date range of 10/15/2009 to 01/13/2009, I need the formula to determine the number of days in each month within the range (October has 15 days in the date range; November has 30, December has 31, and January has 13.) I have a large spreadsheet that would be so much easier to manage with such a formula. Currently, my spreadsheet is setup as follows. I need the forumla automatically fill in the number of days under each month. Stard Date End Date Oct-09 Nov-09 Jan-10 Feb-10 10/15/2009 01/13/2009 I'm using Excel 2007.
View Replies!
View Related
Choose A Name Based On Hours
If you look at "B9" I have created a list of names and when I choose a name their ot hours appear in "K9". Now when I try to do the same in "H9" to bring back there qualification it gives me an error. I also tried to do the same in "M9" for the phone numbers and it didn't work.
View Replies!
View Related
How To Reduce Formula Size/improve Efficiency
how to reduce formula size/improve excel efficiency? I have a really complicated formula with lots of COUNTAs, VLOOKUPs, IFs, etc etc etc which returns a variety of values, ZEROs (for blank cells being referenced), and #NAs. I would like to turn all ZEROs and #NAs into ""s without repeating this super long formula over and over. I am curently using: =IF(or(ISBLANK([superlongformula]),ISNA([superlongformula])),"",[superlongformula])
View Replies!
View Related
Calculate Number Of Hours Between Two Date/time Combinations
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about. For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
View Replies!
View Related
VBA To SHADE Cells Based On: Step-size & Starting Cell
I have a spreadsheet that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user. As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered. For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded. I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:
View Replies!
View Related
How Do I Hardcode A Formula Into A Macro And How Do I Take Report Size Into Account
Please see attached two books, The first book is a report that is generated on my reporting systeM (Report I use everyday) The second report is the one that I have created myself using a macro (macro report) I have to manually manipulate this report everyday and extract some information from it to a custom report(macro report) that we use everyday. I have created a macro for this - please see my code below. I need some help with some small issues I am having,(Highlighted in yellow) 1) I need to include a Total line, but the size of the report varies everyday from say around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below the report, please see attached for an example. 2) I need to hardcord a formula in on Column F and G. But again the report varies in size, the columns do not change but there could be lots more rows. how do i take this into account The formula i need to hard code into column F is =(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007 )+IF($D$60>10000000,0.0005) D60 in the above formula is just the total of all the other months summed together. For Column E the formula is just =F*E But again the size in the report varies.
View Replies!
View Related
Rota - Add Up The Total Number Of Hours Per Week Per Employee
I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee. It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.
View Replies!
View Related
Summing Hours Based On Matching Names
I have an excel sheet which displays the data in the following format: (See image attachment). This is just an example of the way the master list is formatted. The master list has over 18,000 lines. Basically, there's a project name, resource name, and number of hours worked for the project within that work week. I've been trying to figure out a way to create a formula or macro to search all these names and add up the hours of the duplicates. I need excel to search this list of names and total up all hours for each matching name and place each total value on another sheet. For example, Ken Craver has 8 occurances within the week of 1/21/2007 (from project 1 and 25) and his total hours = 31. So the value 31 should be calculated because all the hours associated with Ken Craver add up to 31. Then excel should scan another sheet which has a list of resource names and once it has found the matching name on the other sheet, it will place the value 31 in the column next to the name.
View Replies!
View Related
Solve Between 24 And 48 Hours OR Less Than 24 Hours OR Greater Than 48 Hours
In column A I have a date AND time entered. By the way, this is not via cell format, I have manually entered, say today's date and the current time. In column B I have a future date and time. Basically, column A is the date and time a problem was given to me. Column B would be the date and time I resolved the problem. Now for the formula....Column C needs to spit out whether the problem was solved between 24 and 48 hours OR less than 24 hours OR greater than 48 hours.
View Replies!
View Related
Counts The Number Of Hours That This Person Works & Lookup Function
I'm trying to create a work Rota and I'm having a bit of a problem with a certain section. In Worksheet 1 I have the following headings: Cell A - Name Cell B - Monday Cell C - Tuesday Cell D - Wednesday etc Under these headings is each member of staff and the hours they work, IE L (Late), E (Early), SD (Short Day) etc. In the final Column, it counts the number of hours that this person works (Early is 7.5). In Rows 46, there is a section here to work out how many people are working earlys, which is where I have the problem. The hours are worked out by doing a lookup function on the cell that says E, L etc and goes to a CODES sheet and pulls the value of that letter. What I am wanting to do is lookup that Letter, which and look in the cell next to it and count how many people would be on an early etc.
View Replies!
View Related
Calculate Elapsed Time Based On Business Hours
I've being trying t calculate an excel formula to work out a order turnaround time based on opening hours. Example: The store is open from 8 am to 6pm monday to friday, I need to get a formula to work out how long it took from the time the order was logged until it was completed, during the course of the working day - this works fine but if the order was received on 06/12/07 at 5:20 pm then was finally completed by 7/12/07 at 11:55 the next day I need to figure out how to take into consideration of non working hours during that time. (time from 6pm to 8 am next day example 2 - if order was sent on the 06/12/07 at 01:30pm and completed at 06/12/07 4:30 then I know its taken 3 hours to complete example 3 - if the order was sent on the 06/12/07 at 5pm and completed next day at 07/12/07 at 10am - then I know it took 3 hours to complete
View Replies!
View Related
Hours Worked Based On Multiple IN/OUT Times Per Day
In a single employee Timesheet spreadsheet: ....................Mon.....Tue.....etc. In:................8:00.....8:30 Out:............12:00....12:00 In:..............13:15....13:00 Out:............17:45....17:00 In:................0:00....19:00 Out:..............0:00....20:15 Hrs Worked:.....8.5......8.75. How to format the cells in the daily columns and what formula to calculate 'Hrs. Worked'?
View Replies!
View Related
Formula- Making A 'sports Day' Spreadsheet For A Project
I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum. I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th. [url] basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.
View Replies!
View Related
Formula For Kilowatt Hours
I am creating a comparison sheet for kilowatt hours used, in D29 I have the actual KWH used. What I need is a formula that will give me the cost of the 1st 400 KWH at $0.08570 per KWH in BV7, the 2nd 400 KWH at $0.12175 per KWH in BW7, and the cost of the remaining KWH at $0.14427 per KWH in BX7. Now be advised that some times the KWH may totol 653 for the month and sometimes 1950 for the month.
View Replies!
View Related
Formula To Calculate Hours
im doing an excel spreadsheet. on 1st sheet i have a summary page for some clients, on sheet too i have worked out and done the formula so that it calculats values i put it. i want to take this total value i have worked out (which is a variable- will change from day to day) and move this value to sheet one. from here i want the number that i show in the summary page to deduct from a fixed total in the cell nxt to it. and ideas ive tried several options coming close but no ciga
View Replies!
View Related
|
TRACKER
