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".

View 9 Replies


ADVERTISEMENT

Formula / Conditional Format To Award Score From 0 To 4 Depending On Cell Value

Jul 24, 2014

I want to create a system based on the value of a cell appearing as a grade in the adjacent cell. The cell could contain any value from -10 to +10.

the criteria would be

value greater than +2 = 3
value between 0 and +2 = 2
value between -2 and zero =1
value below -2 =0

I can get a formula to work for a single instance but not multiple conditions.

View 3 Replies View Related

Assign Score To Value By Looking Up Criteria In Table?

Aug 19, 2014

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.

View 7 Replies View Related

IF Function To Display A Message Depending On Percentage In Cell

Feb 21, 2007

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.

View 9 Replies View Related

Lookup Percentage Formula Depending On Price Entered In Cell?

Jan 28, 2013

I have the following table in excel:

Price: Percentage:
$150 10%
$100 12%
$50 14%

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.

View 3 Replies View Related

Using A Percentage Weighting To Create An Average Score

May 26, 2009

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?

View 9 Replies View Related

Formula To Assign Word To A Percentage

Mar 24, 2012

In cell B77 i have a percentage formulated from other information in the sheet. I want cell B78 to show a word based on the percentage score.

As follows:

0%-25%- Poor
25%-50% - Competent
50%-75% - Professional
75%-100% - Expert

View 4 Replies View Related

Assign Consistent Letter Value To Grade Percentage

Oct 9, 2008

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".

View 12 Replies View Related

IF Formula: Assign A Specific Letter To Each Rating, Depending Of The Obtained Number

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

Assign Number To A Cell Depending On Number In That Cell

Mar 8, 2012

Trying to have a formula assign a number to a cell depending on a number in that cell.

Example

In all cells in a range from C8 to G13, if there is a 1, i want the number in C20 to show, if there is a 2, whats in

D20, 3/E20, 4/F20, 5/G20, 6/H20

View 3 Replies View Related

Does The Line Of Code Assign The Value Of Cell A5 To The Range

Dec 17, 2008

Does the line of code assign the value of cell A5 to the range or is it just a startinig point?

View 9 Replies View Related

Assign Range Name Based On Cell Values

Jan 24, 2009

In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).

Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)

Range U28:U46 will assume the name of sheet2A11 & sheet2A4.

Range W28:W46 will assume the name of sheet2A11 & sheet2A5

Right now I am calling the code when something is entered into A11.

I have tried if statement and select case, but I ran into complications with both.

I have posted both codes with the questions I have concerning those codes.

View 6 Replies View Related

How To Assign Range Using Defined Cell In Excel

Nov 22, 2011

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

View 7 Replies View Related

Assign Name To Range Using Variables And Cell Values

May 23, 2009

For example, I have the numerical value "400" in R1C2 and that value would be used in the name "D400" which is to be assigned to R1C1:R5C2.

The code I have is:

Range("R" & DRow & "C1:R" & DRow + 4 & "C2") = "D" & Cells(DRow, 2).Value

but is throwing up the error:

"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.

View 9 Replies View Related

Formula To Work Out Score Based On Score System

Aug 10, 2009

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 Related

Assign Cell Value Based On Another Cell Value In Range

Jul 2, 2014

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 tried the following, but it did not work:

[Code] .....

View 3 Replies View Related

Nested IF (score Range)

Mar 11, 2009

If Average Score is:Then return:
Greater than 89A
From 80 to 89B
From 70 to 79C
From 60 to 69D
Less than 60F

Student's Average Score
90
52
75
88
82
53
46
72
99
100
42
53
69
79
84

=IF(A9:A23>89,"A",IF(A9:A23<=89,"B",IF(A9:A23<=79,"C",IF(A9:A23<=69,"D",IF(A9:A23<60,"F")))))


A9:A23 is student's score 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.

View 3 Replies View Related

Change Color Of Cell Depending On Values In Another Range

Nov 22, 2007

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

g1= 1 g2:g5 = 2,3,4,5,6
h2= 2 h2:h5 = 7,8,9,10

View 2 Replies View Related

Range Copy: Differ Depending On The Active Cell That's Selected

Feb 22, 2009

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

View 9 Replies View Related

Assign Named Range To A Vba Range Variable?

Jan 25, 2013

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.

VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup

MsgBox Range(thisString).Address

Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)

View 1 Replies View Related

Conditional Formatting Based On Percentage And Insert Cell Based On Percentage

Apr 3, 2013

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.

View 2 Replies View Related

Macro To Change Range / Action Depending On Worksheet (range To Autofill)

Sep 17, 2013

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

[Code]....

View 8 Replies View Related

Assign Value Given Another Value In Range

Jan 21, 2013

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.

Test sheet.xlsx‎

View 2 Replies View Related

Assign A Name To A Range Of Cells

Oct 24, 2008

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?

View 5 Replies View Related

Assign Value To Range Object

Aug 19, 2009

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.

View 9 Replies View Related

Assign Range To Variable?

Jul 11, 2014

Why the variable DataRange won't set as below. I get Run-time error 9: subscript out of range.

Code:
Sub CreatePivotTable()
Dim DataHeaderRow As Integer
Dim DataRowCount As Integer

[Code].....

View 7 Replies View Related

Assign Range To Variable

Dec 24, 2007

I am trying to assign a range of cells to a variable but it isn't working. My code is as follows:

Dim VarRateTable As Range

Private Sub CalcBudgets(rng As String)
' Dim VarRateTable As Range
Dim CurrentRng As Range
Set CurrentRng = Selection
Set VarRateTable = Range(rng)

Private Sub NEWCALC(datarow)
Dim vt As Double

vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0)

but the Set statement gives me an error: <method 'range'of object '_global' failed>

View 2 Replies View Related

How To Assign List To Named Range

Oct 11, 2012

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

[Code].....

View 4 Replies View Related

Assign A Non Contiguous Range To An Array

Jan 25, 2009

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,"

View 6 Replies View Related

Assign Loop Counter To Range Row

Jun 22, 2006

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

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved