I am trying to summarise training data in a spreadsheet as per the following layout:
A B C D E F 1 Trainer name # of trainees # of assessments Total hours Avg hrs per trainee Pass rate 2 Peter
3 Paul
4 Mary
The intention is to have this summary fixed on one tab, with the raw data being dumped into a second tab alongside the summary (named “the data”).
The data for the summary comes directly out of an Access database and is in the following format (I’ve put an X against the headings that aren’t relevant to the summary):
A B C D E F G H I J K L M N 1 X X PersID Name X X X X X Trainer Assessment X Result Hours 2
12345 Jimbo
Peter Excel Competent 2 3
54321 Nelson
Paul Access Not yet Competent 3 Notes:
-The data can be up to 2000 rows long
-There will be multiple entries for each trainer of course, and multiple entries for some trainees (i.e. they will complete several training types)
-Result can only be Competent or Not Yet Competent
So, of the 5 fields I need to populate for each trainer, I’ve currently only got the following nailed:
# of assessments: =COUNTIF('The data'!$A$2:$A$114,A2) Avh hrs per trainee = D2/B2
These were the easy ones.
What I’m having trouble with is the following:
For the #of trainees field, the calculation must determine how many people were trained by each particular trainer – so only unique entries should be counted (PersID could be the unique identifier). Total hours will also need to be linked to the individual trainer – in other words, how many hours in total has the trainer spent training?
Pass rate is another tricky one – for each trainer, I’d like to see the percentage of trainees deemed competent out of the total number of trainees they trained.
Background: I play a game that requires 5 members to play, however you can have more then 5 members on the team. In order for a player to receive points for playing they must have played at least 30% of the total games played. The problem for me is when trying to figure out how many games someone needs to play to get to 30%, the total goes up as they play those games.
What I would like to accomplish: I'm looking for help on a formula that would figure out how many games a player would need to play to get to 30%. I have a column that is total games played, %of games played by player, games played by player, and games needed to play to get to 30%. Basically the user would enter the # of games played by each player, and the total games played overall for the team for that week, the spreadsheet would then show the # of games each player that isn't above 30% would need to play.
I tried to make the question as clear as possible, if anyone needs clarification,
Originally Posted by shg Welcome to Oz, Basca
spreadsheet with a representative sample of your data?
Games Played Player 1 20 Player 2 7 Player 3 13 Player 4 20 Player 5 13 Player 6 13 Player 7 7 Player 8 7
I've written the following code. It calculates the future value of required minimum distributions for traditional IRAs. When I enter the function in excel, it drops the last argument. I've tried changing the order of the arguments, and it always drops the last one. The arguments are : marginalTax (marginal tax rate), r (expected return on investment), t (time), begAge (beginning age), RDT (as range -- required distribution table; looks up the percentage required at each age), IRAValueat70 (calcualtes the value of the IRA at 70 years of age)
Function RMD(r As Double, marginalTax As Double, begAge As Integer, t As Integer, RDT As Range, IRAValueat70 As Long) age = begAge endAge = begAge + t Dim holder As Long Dim RMDnow As Long Dim RMDall As Long hat = 70 If endAge < 69 Then Else If begAge <= 69 Then Do While hat < (endAge) RMDnow = (IRAValue / (Application.VLookup(age, RDT, 2, False))) RMDnow = (1 - marginalTax) * RMDnow...........................
Attached is the zip file with the detail of what I am hoping to achieve which is calculating the number of lengths required from generated infromation.
I am trying to create a formula to calculate the total number of calls that I need to receive to hit the target % if the current % is less than the target %. I have tried various formulae, but not having much luck. Also, it shouldn't show me any negative figures.
A = Departments B = Calls Dropped C = Calls Received D = Current % (C/F) E = Target % F = Total (B + C) G = Required (When I enter B, C and E, the rest needs to calculated automatically. G should tell me the amount of calls I need to receive in order to achieve the target specified in E)
Departments Calls DroppedCalls ReceivedCurrent %Target %TotalCalls Required to hit TargetDept 1 21083.33%95.00%12 Dept 2 067100.00%90.00%67 Total 27791.67%95.00%79
i am trying to work with a formula that will look at date today (NOW) and compare this to a due date and in return provide me with only the working hours total. Working hours are 8am to 4pm (8 HOURS).If the due date is passed this will be a negative figure.
Creating a formula that basically has a big IF statement. I hope this makes sense, so here goes. I am looking to find the "age" or "elapsed days" of an ID from when they created an account to when they stopped. This might be very complex and if there's not a way to calculate the Elapsed days (even though that's what I prefer), perhaps there is some formula that can return a value like a "1" or "True" or something that lets me know the statement is true.
original formula from post 3261273 {=IF(D2>0,A2-INDEX(A$2:D$16,MATCH(B2&"1",B$2:B$16&C$2:C$16,0),1),"")}
New formulas needed or revised from the above:
Formula 1: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Create to Suspend but NO Stopped)
Formula 2: If the ID in column 2 are the same, and if there is a value in column 3 (Create) and a value in Column 4 (Suspend) AND there is no value in Column 5 (Stopped) OR Column 6 (Unsuspend), then subtract the dates in column 1 for Create and Suspend. My manual calculation result is in the column called "Elapsed Days (Suspend Only, No stopped or Unsuspend)"
Formula 3: The ID in column 2 does not have to be the same, but if there is a value in column 3 only (Create) and there is no value in the Suspend, Stopped, or Unsuspend columns, then place a "1" in the cell. My manual calc is the "Create Only" column.
Event Date ID CREATE Suspend STOPPED Unsuspend Elapsed Days (Create to Suspend but NO Stopped) Elapsed Days (Suspend Only, No stopped or Unsuspend)
I'm creating an (English) football predictions competition for me and my family.
One problem that has stumped me is how to get the scores based on the 'home' & 'away' score predictions.
The rules are: If I predict the correct exact result I get 3 points. I want to add another 'rule' whereby if I predict the correct winner, I get 1 point. Incorrect predictions get 0 points. I don't know how to do this using a formula.
Using 2003, I'm trying to do the following, data starting in A3, and ending in L3:-
- If A3 is blank, cells have no format - If A3 has a value, with A4 blank, fill is light blue and bottom line solid, top line dotted - If A3 has a value, and A4 does too, fill is light blue, top and bottom line dotted - If H3, which is a date field, is greater than today font is black, if less than today, font is red
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.
I have a script that creates a bunch of sheets. It was working fine when testing because I was just adding a sheet using worksheets.add, but now I've found out they all need to be created from a template sheet. I set up the template sheet and changed to the worksheets.copy method, but I'm receiving a runtime error "Object Required"
All I want to do is copy the sheet and rename it to the next name in the array (I have an array of sheet names).
Here is the code snippet in question (error line in red):
Code:
If lngX = LBound(arrSheets) Then strAfter = Sheet1.Name Else strAfter = CStr(arrSheets(lngX - 1)) Set WS = Worksheets("TmpSht_Checks").Copy(after:=Sheets(strAfter)) WS.Name = CStr(arrSheets(lngX))
As written, the duplicate sheet IS created...
I also tried the method below, but I get the same result (sheet created (but not named) and the same object required error)
Code:
If lngX = LBound(arrSheets) Then strAfter = Sheet1.Name Else strAfter = CStr(arrSheets(lngX - 1)) Worksheets("TmpSht_Checks").Copy(after:=Sheets(strAfter)).Name = CStr(arrSheets(lngX))
I have a table on a sheet called Contracted Staff. Table has staff names down the side and along the top is training needed.
Training H & S. fire. General training Lucy. 5/4/12 Jeff. 5/4/12 Michael. Dawn. 5/4/12
I would like on a training summary sheet to tell me who hasn't completed the training. I know I can use auto filter to select blanks to get who hasn't done it. However I want to print every training and list on the same sheet.
The Master list: Sheet1, Column C has unique numbers, i.e, 0608-211-1093-2.
(can have thousands of entries)
The Comparison list: Sheet2, Column C will have duplicates of some of Sheet1, Column C numbers.
(Sheet2, Column C will have the numbers but other columns will have different data than Sheet1 and may have a few hundred entries or less)
I need a macro that will that will compare the two sheets for duplicates based on Column C and then write the entire row of Sheet1 with the duplicate number to a new Sheet3.
On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list 2. Sheet2; how to remove "B000" or "B00" from the list 3. Sheet3; how to highlight difference or find out the difference between to lists
What I am trying to do here is to compare 2 lists in 2 different files, and when there is a match, then copy and paste the related cells of the matching name. Sorry if this sounds messy, perhaps the sample file I have attached can explain better.
Every month I get a new file in the format of “Data Source” sheet where the list of banks in column A and the figures in column M, AA and AB might change from month to month. For the sake of convenience, I put the source data as a different sheet instead of different file here.
I have an existing report template in the format of “Final report” sheet where basically I copy and paste the relevant cells according to the name of the banks.
I don’t think I can use Vlookup because the cells that I want to extract are not right beside the search criteria. If I’m wrong please correct me.
Anyway, assuming a macro is needed for this, I am wondering if I can create a macro, where it can search the list of banks in column A in “Data Source” sheet based on the list in column A in “Final Report” sheet, then copy the correct cells from column M, AA and AB and then paste them into the correct cells in columns B, E and H in “Final Report” worksheet?
Note that not all the banks in the “Final Report” sheet are in the “Data Source”, so for this example, row 4 for ABN Bank should remain blank after the search because it is not listed in the “Data Source”. The Data Source List might also change over time.
There is also this problem of the bank names from the “Data Source” sheet not being exactly the same as the existing list in “Final Report”. For example in this file, ANZ Bank in the other sheet have all the extra stuff behind, but we know it is the same bank.
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
The formula below works apart from the first bit which i want to be if F23 says 'fail' then i want the cell to say 'no dissertation' but if f23 says pass then i want the formula past that bit to be put into action.
I am an experienced Excel user, but it does not seem possible for this formula to be written all within one single cell. The SUM combines rounding a known value, followed by calculations, and subtracting a rounded number with modified calculations.
This is the formula which does work, although I would prefer for the sake of accuracy (and succeeding in the task of making the formula do exactly what I wanted it to do!!):
I am creating a complex formula with UDF, this Hlookup is part of my complete formula. Meanwhile, I am working on this piece, not Sure what I am doing Wrong
I try to conver "MonthCurrent" Variable in a Year because the MonthCurrent is 06/01/2014, so will be Year(6/1/2014) = 2014
My Range called "DailyRates" is based per Year = 2014, 2015 ......
The number of columns vary, but the number of rows is constant. Lets assume, max_col is the number of columns and max_row is the number of rows per report. Then, I need the following logic as VB macro.
The function LOGEST fits to the equation y=b*m^x. Does anyone know if more complex functions can be found in Excel? Ideally I would like to fit to y=b*m^(x-a) + c. If anyone has come across a method, or some code, which allows this.
