Assign A Score Value To The Cell That Contains A Percentage Depending On A Range
May 2, 2007
I have a spreadsheet with several columns that have percentages as a whole number and with a decimal format. I need assign a score value to the cell that contains a percentage depending on a range.
For example:
If cell A1 has a value between 97%-100% then A2 should display "15", however if A1 has a value of 90%-96% then it should display "12" and if A1 has a value of less than 89% then it should display "0".
I'm trying to find a way to return a value (score) based on the results in a table. I've been trying to use index and matches but am starting to think this will not be the answer for this one.
In the example attached, I have a section called report. For each project, there are criterias which need to be scored based on the values in the lookup table.
For example, project 1 has a result of 200 for criteria 1. On the lookup table 200 in criteria 1 would receive a score of 2. This result is then populated in column L as the score of this criteria.
Im currently studying for an ICT A level and im in my first year doing AS. Im working the AQA board and i have a project that i have a target for an A! I need to create a system for a business and write a report on everything i did...wont go into detail. Anyway. i am having trouble with an IF FUNCTION!!! I have a cell (E35) that contains a percentage.
I want the IF function to display a message depending on what the percentage in cell E35 shows;
IF, 0>> Display "Bad"
40>> Display "OK"
70>> "Good"
90>> Display "Very Good"
E35>=100 >>>> Display "Excellent"
I have tried so many things and I was so sure i entered it correctly, but it keeps coming up with an error.
I'm trying to use the percentage depending on what price I enter in a cell. For example; if I enter $135 then 10% will be used, if I enter $65 then 12% will be used, and if I enter $47 then 14% will be used.
I have 6 different sections of questions, each section having a final average score between 1 and 5.
I would like to somehow create a percentage weighting on each of these sections and then have a final score accross all section, taking into account this weighting?
Newbie here. I have a very frustrating problem. I am using excel for my gradebook at school. I've tried several different ways to assign a letter grade to an number average. It works fine each different way I do it, VLOOKUP, IF, etc. But, I have about 5 grades out of 100 which give the wrong letter grade. For instance, my scale says that a grade of 85 should be lowest limit of a "B", but I get a "C" returned in the cell instead. Like I said, it only happens on a few grades. The biggest majority work fine. I can't figure out why. Any ideas? Attached is one of the "problems" with a student's grades. Note the Final Avg with a grade of "C", it should be "B".
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:.........
How to assign a range in excel using a defined (different) cell.
I've this formula =INDEX($A$1:$A$17,RANK(B2,$B$1:$B$17))
I want to to choose the range from A1:A17 ... i'll use the formula too many times with different ranges for example A1:A21 and i want to know a way to insteed to change it manually each time to make the formula consider the value i insert in a different cell, so i can only change the value in this cell.
for example:
=INDEX($A$1:$A$D1,RANK(B2,$B$1:$B$D1)) so it can consider the value in cell D1
"Run-time error '1004': Method 'Range' of object '_Global' failed
DRow increases dependant on other variable data.
Also, am I right to be using R1C1 instead of A1, or can the same be achieved using A1 references? If so, I think I may not be able to see the wood for the trees.
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.
I'm a VBA beginner In my Excel worksheet, I have 1 column with data (column B). The data is in rows 5-250. This data is student majors.
Now, I want to assign each major a department and display the department in column U.
I was able to complete this for row 5 by using If-Then statements (see snapshot below):
[Code] ......
However, I would have to copy and paste that code over 200 times and replace the B5 and U5 with the correct row number. How can I get the macro to do this for each cell in a specified range?
I am getting the correct answer for the first one only...all others are coming back incorrect. I have been working on this one problem for over 3 hours.
I want to change the colour of a cell depending on its value, when compared to another row of data identiified by a value in another cell. To try an clarify:
b3 = 1 b5=4 Because b3 = 1 then compare cell b5 with the row g1 as b3=g1 then depending on its postion set a colour
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got
'FindRemove = lstRemove.Value 'If FindRemove = "" Then End
' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select
' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry() 'TURNS OFF FILTER IF FINDS ONE ON Dim wks As Worksheet
I am doing an assignment about credit rating and I am trying to make a spread sheet based on Moody's credit rating method.
I have more or less finished my model, but I have one final problem; in the end I will come up with a numeric score, which can be translated into the final rating (Aaa,Aa1 etc.) based on some ranges. I just can not figure out how to formulate the formula, especially because I have no experience working with ranges.
I have tried with some IF formula's, but they seem to be way too long and complex, for what I imagine should be a relatively simple task.
In the attached sheet I have a final value of 6.9, which should give an A3 rating given that it is within the 6.5 - 7.5 range.
Is it possible to assign a name to a range of cells, so that when you insert a formula into another cell you can call that range of cells into the formula?
I'm doing somthing basically wrong here but i'm not sure what, everything seems to work as planned except in each case where a value is assigned the value isn't actually assigned. any ideas?
The basic objective is to convert a list of numbers (1 to 3 digits) to the format "000-" i.e. all three digits with a hyphen at the end.
I have the following VBA code which created a unique list of values in a data column. After list is created, I want to assign it to a named range ReferTo parameter. Currently I am getting compiler error. How can I assign this unique list to a named range?
VB: Dim UList As New Collection Dim rCell As Range .... If TypeName(Selection) = "Range" Then
When I insert a line to select the range on the Worksheet it does select the range named as temp. However, the array does fill but only with the first part of the range eg in this case A7:C44.
My aim is to create an array of size point1A( all the rows in the multiple ranges, 3 columns).
Example of a Range to be assigned to the Array point1A. temp = "A7:C44,D7:F44,G7:I44,K7:M44,N7:P44,Q7:S44,"
How do you assign a loop counter value to a range row value? Scenario: Add cells from a column that aren’t successive. SUM C10 + C17 + C24 etc
Private Sub CommandButton1_Click() Dim total As Integer Dim counter As Integer For counter = 10 To 700 Step 7 total = total + Range("Ccounter") Next counter End Sub