I tried to use an array to calculate the mean score for a column: if a cell has a value 'A', then it is assigned a value 1, if 'B', then 2, if 'C', then 3.
But this resulted an error.
File attached here. Example.xls‎
i need the formula in colums (l) it needs to find the agents name and the calulate the scores for that agents name
View 8 Replies View RelatedI have a table of data where each row is a person, and each column is a group that person might belong to. In the intersecting cell of a person row and group column is text showing what position they hold in that group (eg Director, Head, member etc). In many cases the cell is empty because the person is not involved with the group.
There are then two lookup tables which have scores; one for the group name and one for the position type. The group name is always the column header. I have used these lookup tables as I would like the scores for each group or position to be easily adjustable.
Probably best that I just attach an example. I tried to solve this using SUMPRODUCT. VLOOKUP on the two lookup tables would have been good but it seems it doesn't work with arrays the way I expected. My attempt (which doesn't work obviously!) is given.
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
View 5 Replies View Relatedi have three sheets in this workbook. i want to filter the the pupils with less than 20 marks in total at the end and copy it to the page named filter, from which i will go to the sheet1 which gives a remedial plan for ONLY those pupils who come in the filter page. this is all good but the problem is that when i update the Cont page, it wont update in the filter page.
View 6 Replies View RelatedI have written the code below. But it didn't work. In my opinion the error is most probably caused by irr worksheet function. Because the code is actually bigger and I tried it all part by part.
Option Base 1
Dim i As Integer
Dim j As Integer
Dim counter As Integer
Sub General() 'Makes general calculations
Dim Cash_Flow_Project_Entity(49) As Variant 'Toplam Yatýrým Nakit Akýmý
Dim FIRR(49) As Variant 'Yearly FIRR
Analysis_Period = Worksheets("Veri"). Cells(15, 5) 'Takes " analysis period" from "Veri" Worksheet
End Sub
I have an array formula in the attached file to calculate the average of the Top 3 (cell F6). I put that same formula in cell F14 to calculate the average of the Bottom 3 (just change the "large" to "small", however it doesn't work. The result shows zero. How do I fix this?
View 10 Replies View RelatedI am trying to calculate the Standard Deviation of an array. However, to populate the array, I have the following:
Count Number 1 Count Number 2 CountNumber 3
1 100 2 200 3 300
For example, this above means that there are 1 100's, 2 200's, 3 300's. I would ideally like to populate an array that is {100,200,200,300,300,300} based on those 6 columns that provide a count and a number. Then, I would like to calculate the standard deviation based on those (in this case it is 74.5).
array.jpg
I need to multiply matrix variable by a constant (each matrix entry has to be multiplied by the constant).
Sub Matrix()
Dim X As Variant, Y As Variant
Dim a As Integer
a = 2
X = [1, 1, 1; 2, 2, 2; 3, 3, 3]
Y = X * a ' Here it writes that type is mismached
End Sub
I read that in cell functions it is possible to do such calculations.
If the data looks like this:
10
7
6
10
9
9
3
What would the formula be to calculate that array of values = 57% because 4 of them are equal to or greater than 9, and there are 7 of them in total? The formula should accommodate however many numbers are listed and calculate what percentage of them are 9 or 10.
I am looking for ideas on how to speed-up one workbook. I have a spreadsheet with hundreds of lookup formulas and array formulas which are very slow to recalculate (takes around 5 min). Good thing about it is that these formulas are located in a specific range and that I need to run them only once a day (in the morning). Since I need to refresh the rest of the spreadsheet frequently, I am looking for an idea on how to isolate this resource heavy areas.
One solution I've been thinking about is moving these complex and slow formulas to VBA and run them only when needed. Can anybody assist me with the best and easiest way of doing this. Ideally I would want to leave option to the user to later edit this formula. Does anybody have any experience with weather it is best to migrate complete formulas to VBA or maybe leave formulas in the spreadsheet and run them based on predefined flag (that I can switch on or off from the code)?
I I have a excel document which has a sheet containing a large number of array formulas.
I turned the automatic calculation off.
However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.
The majority of the array formulas are IF queries with multiple criteria.
Would it speed up the calculation if I didn’t use array formulas?
On one sheet (KPI) I have either the values "x" or "" in the range A84:A89 to mark wheter to use the corresponding project in the range B84:B89. On the sheet X-ref I have the same project names in range T4:Y4 and a corresponding target value in T8:Y8
What I want is the sum (or average) of the marked-projects target-values. The result should end up in KPI!G31.
In other words I want
=sumif(A84:A89;"x";'X-ref'!T4:Y4)
but it doesn't work since the first range is an column-array and the second range is an row-array.
I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.
One method has been using this sumproduct formula:
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr
Customer #1 1 1 3
Customer #2 1 3
Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
I love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I would like to display the column header of the row in which a value other than 0 exists in a cell for each instance that value exists in an array spanning 3 columns. So the result cell could be any of the three column headers.
I started the formula in P2 of the GL Detail-2012 tab. (FILE TOO BIG)
Excerpt:
Resin Sand Eliminated Segment
- (12,896.65)-
- (6,570.85) -
- (11,503.80) -
- (13,188.50) -
If there is any value other than 0 in Resin, Sand or Eliminated columns, return that column's header under Segment.
Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,)))
I'm currently using this formula to calculate the average values in column B where the value in column A ='s E1 eg:
=AVERAGEIF($A$2:$A$21,E1,B2:B21)
Instead of doing this however, I need the formula to calulate the average from column B where the "Date" in column A ='s the year and month I specify in other cells.
Year value specified in: F1
Month value specified in: G1
I have an array 20 Rows x 42 Columns, which contains a competition draw.
I need to search this array for a unique value and return whatever the time is in the first column on the same row as the value appears, and enter it into column C in the Womens Times sheet.
I also want to return into column D the court number from row 3.
The reason i want this automated is as teams enter / withdraw we may need to drag the games from court to court to fill gaps, so i want the Womens Times sheet to update accordingly.
I have been messing around with index and match, but cannot quite get it to return what i need.
I have attached an example ... on the sheet "Womens Times" in column A there is a list of game numbers ( #1W etc etc) indicating womens game #1 and so on. The main sheet i am using also has a seperate tab for the mens games, hence the designator of W or M on the end of the game number.
I have Column A and B
I want to calculate the total of Column B where by there Column A shows figure "2929".
im using spreadsheet works which seems to be very similar to excel. i am making a table full of numbers and i want to count how many times the number 1 appears and for that amount to be displayed.
View 11 Replies View Relatedi was assigned to make a computation for our individual scores using this:
EMPLOYEES A-N
inputs:
SURVEY(0-100)
LH(lost hour)
AHT(average handling time)
compute for:
SCORE
RANK
the SCORE has a total of 100%
so to get the SCORE, each input gets a percentage: SURVEY=40% LH=30% AHT=30% then added.
but the thing is LH and AHT has an equivalent.
LH
0 to 1=100
1.1 to 2=90
2.1 to 3=80
3.1 to 4=70
4.1 to 5=60
5.1 above=20......
eXAMPLE:
EMPLOYEE A
SURVEY(0-100) = 33.00
LH(lost hour) = 12.00
AHT(average handling time) = 14.20
SCORE = CODE
RANK = CODE
now for the rank i just use the code =RANK(SCORE A, SCORE A:SCORE N)
im lost on how to compute the SCORE though coz i can just use =((SURVEY*40%)+(LH*30%)+(AHT*30%))
but the one that needs to be on the LH and AHT are the equivalents and not the inputs.
and there is 1st 2nd and 3rd place. the name of the top 3 employees should be shown under the table.
i have a list of names in column A and a corresponding score for each name in column S.
on a stats sheet B10:B15 was trying to make a top 5 list using =LARGE(Week1!S15:S46,1) ... with Large i am able to get my top 5 from the week1 sheet but how can i assign the corresponding top5 persons from column A15:A45 in week1 to the stats sheet in column A10:A15
I am currently working on a score sheet with list of question.
I have:
On Sheet 1 (will be hidden later)
List of 14 questions (e.g. question 1 = 6 possible answers, for answer 1 is score -2, for answer 2 is score 7... etc).
In column C are possible answers, in column D are the score values.
On Sheet 2 (will be visible for participants)
Drop-down list based on Sheet 1 questions (Sheet 1; Column C)
I have set Cell link for each question so when you choose the 3rd answer, there is number 3, but I need to set different values based on Sheet 1, could be that 3rd answer is score 12. I do not know which function to use (I tried IF, but to me it seems too long and difficoult).
I've seen a few bowling formulas within mr. excel but nothing in code. so i tried it and the only problem i have is when a player strikes.....then the code should add the next two balls thrown..... it works if the next two are strikes but not if there is no 'mark'. So basically its not adding the second ball. The code looks right unless I'm just missing something. heres the code....
View 9 Replies View RelatedI am using data validation for a drop down list e.g Good, Average, None. What i want is when a user selects for example "Good" i need a score to be automatically entered into another cell so that i can then do some calculations.
View 2 Replies View RelatedI have one excel file with three sheets marked 1,2 & 4
Sheet 1. is the place we add the description and the member number. When a member number is entered it gets the member name from sheet 4.
Sheet 2 is the page I need to change. Currently we can enter any score number in each box between 1 to 5. If we try to enter other numbers we get an error message.
I need to change the form so we can enter 1 to 10. Any other number needs to generate the error message.
Should be an easy fix for experts, but I'm not one of those...
I'm trying to create a "scoresheet" in Excel where a racer has a name and an ID number on the main tally worksheet, with columns relating to their scores for each race. Subsequent worksheets have results from each race (one race per sheet), with the individual referenced by their ID number.
How do I have the main tally sheet "find" that person's score for each event in separate columns so that it can add up their totals? I tried an IF formula to find each score in the subsequent sheets, but that didn't work.
Is there a simple formula to do this? I've been achieving the results by importing the worksheets into a Filemaker database (where the ID number "matches" and updates each score), but that's a pain, and I'd rather do it all in Excel.