Array Formula- Performance Rating
Jun 26, 2007
I have a list of employees who each have a performance rating (very top, consistent, etc). Each employee is also either above the midpoint of a salary range or below (which is represented by a separate column with 'Yes' for above and 'No' for below).
What I am trying to do is apply a separate set of %'s for each group of employees. So, for the employees who are below, I want to return 10% and 8% for each respective rating, but for those employees who are above, I want to return 8% and 6%. (for example).
View 9 Replies
ADVERTISEMENT
Nov 20, 2013
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
View 1 Replies
View Related
Jun 20, 2007
I'm trying to write some code that is performing operations on large arrays, using a pair of For loops. In languages like C++ and Fortran, it makes a big difference to the performance of the code which way round you put these 2 For loops, due to the way that array data is stored in the computer memory. However, for VBA, I've no idea which way round this is. For an array, A(i,j), do I want
For i = 1 To N
For j = 1 To M
A(i,j) = Cells(j, i).Value
Next j
Next i
View 9 Replies
View Related
Nov 3, 2009
I've different rating matrix as below;
more than 90% - " Excellent"
80% to 89% - "Very Good"
65% to 79% - "Good"
below 64% - "Low"
hence if any of the rating falls in a cell need a formula to corresponds that (lets say in cell "A1" if the rating is 85% then result should be "Very Good")
View 9 Replies
View Related
Oct 3, 2008
I'm trying to get a formula working that is basically for a rating chart. The chart is somewhat simple, but the formula is a bit complex (for me anyway)
So the details are the rating is on three axis with three levels, and here is what I need to do.
At the moment the formula below works, but it isn't 100% accurate because I actually need to take an average of F5+G5 then take that value and add it to H5 then divide by two to get the proper average of the two charts. (if that makes sense.
=IF(AVERAGE(F5:H5)>2.33,"High",IF(AVERAGE(F5:H5)>1.67,"Medium",IF(AVERAGE(F5:H5)<1.67,"Low")))
Here is the formula that I thought would have worked, but Excel begs to differ
=IF(SUM((AVERAGE(F5:G5))+H5)/2>2.33,"High",IF(SUM((AVERAGE(F5:G5))+H5)/2>1.67,"Medium",IF(SUM((AVERAGE(F5:G5))+H5)/2<1.67,"Low")))
I played with the brackets quite a bit thinking I had something in the wrong order, but nothing seems to work. I always get FALSE in the field and an error as well when I enter the formula.
View 7 Replies
View Related
Jun 15, 2009
I have some delivery ratings from suppliers, and I want to assign a specific letter to each rating, depending of the obtained number. Criteria is:.........
View 4 Replies
View Related
Nov 6, 2008
I am trying to work out a % score for a telephone operator. To explain further, I would monitor a call that an agent takes. The agent starts at 100%. There are 20 points to be scored and they are scored like this
1 - Yes
0 - No
left blank - n/a
B3 counts the number of entries in the column
B2 counts the number of 1's in the column
B28 = B2/B3
Seems to work fine until the agent makes a fail which would enter a 0 in the column. Somewhere I think I need to take account that there are 20 possible fails or passes?
View 6 Replies
View Related
Sep 8, 2009
I need formula to calculate a fee based on performance. For example I have 4 unique keys with the following performance:
KEYPerformance
2010,000
2120,000
2230,000
2340,000
I need to work out how I can formula drive a fee calculation based on performance which is subject to different ranges:
KEYMin FeeFee 1Fee 2Fee 3Fee 4
201000.10.20.30.4
211000.10.20.30.4
221000.10.20.30.4
231000.10.20.30.4
For example key 20 has a min fee chargeable of 100, however a fee is chargable based on performance as follows:
Fee Range 10-9999
Fee Range 210000-19999
Fee Range 320000-29999
Fee Range 430000-99999999
So key 20 gets charged 0.1% of amounts between 0&9,999, 0.2% of amounts between 10,000&19,999, 0.3% on amounts between 20,000& 29,999 and 0.4% on the rest.
How can this be combined into a lookup/range/low-high formula to extract the correct values????
View 13 Replies
View Related
Nov 23, 2013
Im looking for a ELO rating. Its a system which they often use with Chess.
View 3 Replies
View Related
Sep 3, 2008
I have an interest in horse racing and I use spreadsheets to help with my form study. I have reasonble knowledge generally of but am self taught.
What I want to be able to do automatically is to re rate 6 columns (shown with yellow at the top)(columns BA-BF)in a worksheet after scratching occur. I have away of eliminating the ratings out of the 6 columns after scratchings but would like to rerate the columns so the ratings start at 1 and rate up. Now when a zero appears it needs to stay a zero as that means it has no rating but where a blank occurs it would indicate a scratched horse. I want to keep the number of rows without deleting any for the scratched horses so it matches other worksheets I use and also need to keep the horses in race order.Hope this makes sense.
View 2072 Replies
View Related
Jun 15, 2014
I am trying to display result of risk assessment matrix in excel. I have three columns PROBABILITY , IMPACT and RISK RATING (probability *impact).
Probability = Very Low(1), Low(2), Medium (3), High (4), Very High (5)
Impact = Low(1), Medium(2), High(3)
Risk Rating = Low (value between 1 -3), Medium (value between 4 - 6), High (value between 7 - 15)
Is there any way that I just use TEXT in the cell (visible to user) and excel does its magic in the background using the numbers that I have for each TEXT? I don't want to display numbers.
View 2 Replies
View Related
Dec 16, 2008
I have a list of issues and depending on their priority would require a different target closure date e.g. low priority = 90 days, medium = 45 days, High = 10 days.
So if I have the following columns:
Issue No (Unique Ref #)
Date Raised
Priority (Low/Medium/High)
Target Closure (based on the pre-determined criteria as detailed in above paragraph)
Can somebody suggest a formula to enter in the "Target Closure" column so it is automated?
View 7 Replies
View Related
Apr 7, 2009
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
View 6 Replies
View Related
Nov 6, 2013
Is there anyway to recreate this formula w/o it being an array ?
{=IF(C3="","",IFERROR(INDEX('Master List'!$B$1:$B$2000,MATCH(TRUE,ISNUMBER(SEARCH('Master List'!$A$1:$A$2000,C3)),0)),"ADD TO MASTER"))}
View 5 Replies
View Related
Nov 10, 2006
I have a relatively small Excel workbook that is using numerous lookup and Match function combinations. I also have a number of dynamic ranges defined and a few UDFs.
Problem is I am encountering some fairly severe performance issues. Changing a single value on a sheet is taking ages.
I suspect it may be due to the workbook dynamic ranges recalculating. Is there a limit to the amount of dynamic ranges in a workbook? And when do they get recalculated? Could it be something to do with sequencing of the calculations?
View 3 Replies
View Related
Oct 2, 2008
Morning all. I'm trying to use VBA to generate autoshape arrows to track changes in values.
Something allong the lines of Column B (Jans data) C (Febs Data) E (autoshape arrow, green up, for improvement, or red downwards for a worsening).
The problem I'm having is postioning my autoshapes. The only way I can do it at present is to keep all columns and rows a standard width, and position using multiples of those.
Is there anyway to set the autoshape to the cell height and width, and position it within the boundries of the cell? This way I don't have to worry about rewiting my code every time I change a cell width?
View 7 Replies
View Related
Nov 23, 2009
I am encountering a situation wherein each line of VBA code is essentially compiled as I type on that line, instead of when I move to a different line. For example, if the code I entered is incorrect, the compiler waits about half a second and then turns it red. Also, if I enter a space, it deletes it immediately.
View 4 Replies
View Related
Jan 17, 2013
I have thousands of rows of data sorted chronologically in a flat list. What I'm looking to do is have a pivot table or formula which picks out the highest value for a given person. Sound simple?
Okay,
- column A: date
- column B: name
- column C: exercise weight
- column D: exercise rep's
- column E: exercise predicted max (essentially, weight x reps)
Now, I have a pivot table which picks out each persons highest "exercise predicted max" (right click on table, field settings, MAX) but I want to know the actual weight they've lifted and how many reps they've done with it.
So basically, I want a simple table which shows me the date, persons name, heaviest weight they've lifted and how many reps they did with it. I've used the CONCATENATE function to group the "exercise weight" and "exercise rep's" in to one cell but the pivot doesn't return any values (all cells in pivot = 0).
View 1 Replies
View Related
Jun 4, 2008
I run a simple macro loop to clean some data across nine columns. The purpose is to collapse the data in the columns so that column 1 has the first value found in that row, for the set of columns. For instance, if columns 1-4 are empty, it deletes / shifts everything left until the first column is not empty. Then it goes to the next row and repeats. Data can range from a few rows up to 6000.
[I have a period in the data as the cell content to evaluate]
Sub A_Rollup_collapse()
StartT = Now
Dim Col As Integer
Col = Range("IV1").End(xlToLeft).Column - 9
LastR = Range("A60000").End(xlUp).Row
Application.ScreenUpdating = False
For R = 2 To LastR
Do While Cells(R, Col) = "."
Cells(R, Col).Delete Shift:=xlShiftToLeft
Loop
Next R
EndT = Now
Application.ScreenUpdating = True
msg = "Done. Duration: " & Format(EndT - StartT, "h:mm:ss")
MsgBox (msg)
this macro (B) runs after another macro (A) that populates the nine columns with data using vlookups. Macro (A) It builds out a chain of information from col 2 to col 9, converts to values etc. Nothing odd.
When macro (B) is called right after running macro (A), it can take about one minute for 500 rows of data.
When I save and close the workbook, reopen it and run macro (B), it only takes one second.
When I insert a ThisWorkbook.Save between the two call statments, macro (B) still takes over a minute.
A minute is not too bad but when I'm dealing with thousands of rows, the difference is more like 30 seconds vs. 9 minutes which is a problem.
View 9 Replies
View Related
May 1, 2009
I've a sheet that records performance on individuals, what it does is collate the performance daily on certain things.
Most are simple plus/minus one and i have control buttons that do this,what it does is: On click it adds one to a different sheet, or if to remove it minuses one, and there is a total box to sum these figures.
But there is also some provision to add collected money value, and what i would like to do is have a function that deletes the last figure added to the financial transactions
This is the code
If Target.Address = "$A$6" Then Sheets(2).Range("A65536").End(xlUp).Offset(1).Value = Target.Value
So for example
in A6 £10.50 is added, but i can't think how i can provide a service that will delete the last figure in Sheet 2 range A, as this will be constantly changing.
View 9 Replies
View Related
Aug 19, 2006
I have built a fairly complex app in excel which takes 50 webqueries and makes a table out of specific data. It generally takes ~34 seconds to run through the webqueries, and when its done, it starts all over and runs the 50 queries again on 30 second delays. My problem is that for about 5-10 of these cycles, everything works fine, but after that, the time per set of queries starts going up...in the range of 2:00 or more for the same 50 webqueries that originally took ~34 seconds.
Without posting each of the userforms and modules, is there a general way to clear out the memory or see whats bogging down the process or anything between sets of queries to maintain performance? the webquery itself is a
With ActiveSheet.QueryTables.Add(Connection:= "string" & variable
statement that gets called each of the 50 times. The only thing I can think of is that somehow variables or some other procedure isnt clearing after each of the sets of 50 queries runs. Is there any way to check that or make sure it is cleared before it begins the 50 query set again? I noticed this mostly by watching my "physical memory avaliable" drop a small amount each time the set ran, and I had to quit excel before it would return to its original amount.
View 6 Replies
View Related
Aug 24, 2006
why it takes about 100 times longer to set a value using a named range. The code below demonstrates the huge difference between Range("A1") and Range("my_range")
Public Sub testloop()
Dim counter As Long
Dim start As Double
start = Timer()
Application.Calculation = xlCalculationManual
Application. ScreenUpdating = False
For counter = 1 To 34000
Range("my_range") = 9999 ' 101 seconds
'Range("A1") = 9999 ' 1.2 seconds
Next counter
View 7 Replies
View Related
Mar 4, 2014
What I'm trying to do is show monthly performance based on products of a few different areas. Where I'm running into problems, is with the month listings. If the report shows one month at a time, I can get it to work with SUMIF formulas, however when there is more than one (and there will always be three).
View 6 Replies
View Related
Jul 3, 2014
I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.
The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.
I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.
View 4 Replies
View Related
Aug 23, 2006
Im having slow performance reading a CSV file into 34000 sheet named ranges. Currently it takes about 8 minutes to read 34,000 records. After stepping through the code Ive realized there are two bottle necks.
1)finding which sheet that the range name resides see getRangeAddress() and
2) actually setting the value to the named range i.e. Range(ra) = dprecord(1)
The CSV file looks like this: <range name>,<data value>
e.g. DPA_1001,99090
Performanace actually seem to slow as the macro runs.
Public Sub readDatapoints()
'Macro readDataPoints
'This macro will read in the a comma seperated value (CSV) file of datapoints.
Dim sFile As String
Dim currentLine As String
Dim delimit As String
Dim counter As Integer
Dim ra As String
Dim fs As Object
Dim ts As Object
Dim dprecord
Dim oldStatusBar As Boolean
delimit = ","
View 9 Replies
View Related
May 18, 2007
I just wanted to concatenate a few cells using an array formula like this:
{=SUM(IF(B2:J2="";0;B2:J2))}
Well, this does not work. I have no idea why it fails. Any solution to this without scripting?
View 9 Replies
View Related
Jun 30, 2006
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it
in writing so that i accurately describe my problem. attached is my example
View 4 Replies
View Related
Jan 3, 2014
Looking to create a system to measure KPI (key performance indicators). I need to include a weekly target and then measure performance for that week against the target. I want to create a form to make data entry simple, how best it would be to implement using excel. Would I have 2 tables, one for targets and one for performance and then use look ups, [URL]
View 3 Replies
View Related
Jan 11, 2010
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:
We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
View 3 Replies
View Related
Apr 16, 2006
I have a data base for a stock.
It goes like this:
Date Price
Date1 Price1
Date2 Price2
How can I do an analysis of the:
Worst 12mths performance (like how much is the maximum a stock drop in any rolling 12mths period)
best 12 mths performance
View 4 Replies
View Related
