Assign A Score Value To The Cell That Contains A Percentage Depending On A Range
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
IF Function To Display A Message Depending On Percentage In Cell
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 Replies!
View Related
Assign Consistent Letter Value To Grade Percentage
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 Replies!
View Related
Assign Range Name Based On Cell Values
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 Replies!
View Related
Assign Name To Range Using Variables And Cell Values
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 Replies!
View Related
Formula To Work Out Score Based On Score System
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 Replies!
View Related
Nested IF (score Range)
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 Replies!
View Related
Range Copy: Differ Depending On The Active Cell That's Selected
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 Replies!
View Related
Assign Value To Range Object
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 Replies!
View Related
Assign Range To Variable
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 Replies!
View Related
Assign A Non Contiguous Range To An Array
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 Replies!
View Related
Assign Autofilter Results To A Range Object
I have a simple three column range. I Autofilter the range based on one of the values in Column 1. I then want to grab the results into a range object. I've been trying to use the Specialcells(xlcelltypevisible) route to no avail. It only gets one row when I should have many.
View Replies!
View Related
Assign Numerical Values By Letter Across A Range
I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4. =SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4) This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0. Example: J2...K2...L2...M2...N2...O2...P2...Q2...R2... A....B.....B.....C.....B.....D....B.....C.....E the output of the above sequence should be 1+1+1+2+4+4+0+4+4+0 = 21
View Replies!
View Related
Assign Loop Counter To Range Row
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 Replies!
View Related
Create/assign Outlook Tasks From A Range Of Cells
I have had some help with this (thanks!) but am stuck. Can someone add to the code below and make it so that multiple tasks, with multiple assignees, can be created from a range of cells, such as: Column A Column B Row 1Task SubjectAssign to Row 2GV LP for AZJohn Brown Row 3GV LP for COJohn Brown Row 4GV LP for ILSuzy Smith Row 5GV LP for INSuzy Smith Row 6GV LP for KYSuzy Smith Row 7GV LP for MNSuzy Smith Row 8GV LP for MSBob Barker Row 9GV LP for NCBob Barker Row 10GV LP for NJBob Barker Row 11GV LP for NMBob Barker Row 12GV LP for NVBob Barker Row 13GV LP for NYBob Barker Row 14GV LP for OHBob Barker Row 15GV LP for SCBob Barker Row 16GV LP for WVBob Barker (Sorry, it didn't translate well. The "GV LP for AZ" is cell A2, "John Brown" is cell B2.) Here is the code I have so far:
View Replies!
View Related
Assign Named Range To Chart Series Collection
I am trying to assign a named range to a series on a preexisting chart in a sheet, but I get the error that "unable to set values property of series class". This is the code example that DOESN'T work: Sub assign_values() Dim myrange As range Set myrange = range("a1") Set myrange = union(range("a5"),myrange) activechart.seriescollection(1).values = myrange End Sub However, this code DOES work: Sub assign_values() activechart.seriescollection(1).values = range("a1") End Sub I don't see why the first wouldn't work, but the second would work. I suspect it is something to do with the union function. i am trying to go through a set of data and then group certain cells into a named range to be used on the graph.
View Replies!
View Related
Percentage Of 1's To Zeros In Range
I ahve compiled a spreadsheet that calculates how many wins an individual has as part of a team. I am entering '1' if they win and '0' if they lose but auto sum obviously doesn't add the 0's up to calculate how many matches have been played in total. I need the total matches played because I have a formula in place to calculate % of matches won. Is there a formula I can use to add 0's and 1's or YES & No's up to total the amount played?? ( Adding up a vertical column. )Please help. I've spent days trying and I'm getting nowhere fast.
View Replies!
View Related
Macro To Calculate Range By A Percentage
I'm trying to do a forecast of my organization's budget for the next five years and need to create scenarios where certain figures will either be increased or decreased by a specified percentage that varies from 5% to 20%. I have recorded eight macros that can accomplish this task, but I feel like there must be a more effienct method. In addition, I would like to increase or decrease the figures from either column H or column J. Column R is the results column. This is the macro I recorded that provides for a 10% decrease:
View Replies!
View Related
Finding Percentage Of A Range Of Values
I have a little table: Columns A:B (separated by commas) Row 1- Year One, 20 Row 2- Year One, 20 Row 3- Year One, 10 Row 4- Year One, 30 Row 5- Year One, 20 Row 6- Year One, 20 Row 7- Year Two, 10 Row 8- Year Two, 20 Row 9- Year Two, 20 Row 10- Year Two, 10 Row 11- Year Two, 20 Row 12- Year Two, 40 In the adjacent cells in columns C (C1:C12) I need a formula that would turn the value into a percentage of the total of the values for that year (e.g. in C1: 20 is 16.7% of 120, so the value displayed would be 16.7% (or 0.167)).
View Replies!
View Related
Percentage Of Numbers In Range Greater Than Another
I have two columns A and B. In each column are a list of numbers. I want to count the number of times the numbers in column A are greater than the numbers in column B and display it in the form of a percentage. An even easier way for me to do what I need is as follows...I have a third column (column C) that subtracts A from B...if it is a negative number, the cells in column C are autoformated to color the cell red. If it's a positive number, the cell is colored green. Is there a way I can count the number of cells that are red and divide them by the number of cells that are green to get a percentage?
View Replies!
View Related
Assign Range Values To Control Toolbox Combo Box
I've built the code for a Template form for data entry of a survey. Through Control Toolbox I've created the form and used several textboxes, option buttons, checkboxes and COMBO boxes this one in turn being my trouble. - Sheet one is called "Data" (this is the place where all inserted information is going to be stored - Sheet two: "Variables" (here is where I keep the required values for the como boxes - inserted manually apriorely... Question: What is the code to assing to the combo boxes in order to have the values from the sheet "Variables" of the range A1:A4 - use the comboboxq2 for referee, I'll do the others P.S. see the code up to now: Private Sub CommandButtonN_Click() Dim eRow As Long Dim ws As Worksheet Dim inf As WorksheetFunction Set ws = Worksheets("Data") Set ws = Worksheets("Variables") ' Find first empty row in database survey eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ................
View Replies!
View Related
Count Specific Percentage Group In A Range
I have a column of equipment average utilization percentages. This column is a dynamic name range called "Avg_Util". Values in this range were created by a calculation that results in a value of "", for equipment which have no utilization as yet, to those with a utilization of anywhere between 16% to 160% for those with actual hours in service. I need to count how many have a utilization of <=50% and those that have a utilization of >=150%. COUNTIF didn't work because it apparently counts the "" values (as zeros?)? I didn't fare much better with SUMPRODUCT either, but that could just as easily be the fact that I'm still new to that function. I get the feeling I'm overlooking something regarding working with percentages here to be having so much difficulty with something that just doesn't seem like it ought to be that hard.
View Replies!
View Related
Print By Date Depending Range
i have a excel sheet which i use to schedule my service team. Basically i have horizontally the dates ((a colum for every day of the year) and vertically the guys names (about 25 rows). I would like to add a function so i can push a button and i automatically print the past, current and next week of my shedule on one page. In addition it should print my usage chart which is on a different work sheet in the same file.
View Replies!
View Related
Set Range Depending On Value In Column D
I have a list of data in colum A and I want to group them together. In column D I number the group with the same number. A1 = Tony D1 = 1 A2 = Tom D2 = 1 A3 = Bill D3 = 1 A4 = Helen D4 = 2 A5 = Marg D5 = 2 A6 = Kathy D6 = 2 A7 = Catherine D7 = 2 And so on..... The list up to 100 names As you can see group 1 = 3 names, group 2 = 4 names, group 3 = 6 or maybe 7 and so on. I'm trying to set ranges for them Eg: set rng1 = A1:A3 set rng2 = A4:A7 but it not fixed to set ranges like that. It's variable and changes every week. Next week maybe rng1 = A1:A5, rng2 = A6:A9. can I have the VB code to determine these variable ranges.
View Replies!
View Related
Assignment Of Number Depending On Range It Falls Into
The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.
View Replies!
View Related
Lookup Date In Range Depending On A Number
I have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
View Replies!
View Related
Percentage Of Total For Cells In A Range Equaling A Specific Numeric Value
I am trying to create a formula that shows a percentage of cells within a range that contain a particular number. This percentage is based on another cell, which is the total number of all audits. Each audit has rating columns, with a numeric value indicating it's rating. I need to count those cells within the range that equal the numeric value assigned to the rating, and then divide that total by the total number of audits to obtain a percentage of calls with each particular rating in each category. Here is an example of what I'm trying to accomplish: A1 = 2 (Total audits) B1 = 07/31/2009 (Date of first audit) C1 = 3 (Rating of first category) D1 = 1 (Rating of second category) E1 = 2 (Rating of third category) F1 = 08/03/2009 (Date of second audit) G1 = 0 (Rating of first category) H1 = 2 (Rating of second category) I1 = 2 (Rating of third category) The percentage of audits within the first category that have a rating of 3 would be 50%. The percentage of audits within the second category that have a rating of 3 would be 0%. The percentage of audits within the third category that have a rating of 2 would be 100%.
View Replies!
View Related
Return Range Of Numerical Values In Single Column Based On Frequency Percentage
I have a single column of numerical values that may repeat many times within the column. I require a flexible Formula: Use an Input Cell for the specified and changeable Percentage(s) %. Column may be filtered – so only take into account Visible Filtered Cells. The Formula will calculate and Return the numerical range of values that fall between the specified percentage % (using the Input Cell) e.g.; 70%. The Formula should Return two numeric values: a Start Value and an End Value – NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the values that appear 70% of the time in the column; therefore, taking into account Repeat / Duplicate values. The calculated Results: the two numeric values will be returned to separate cells on a new Sheet.
View Replies!
View Related
Formula To Copy Lines Depending On Date Range And Agent Selected
What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame. Here is an example of the work sheet where the data is originaly. [Removed] Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010. [Removed]
View Replies!
View Related
List Box Depending On Combobox And Add Or Delete Items To Range
i had a user form with a Combo box,list box,text box and a command button. I need the code that works upon selecting: 1)An Item from the combo box should display the list in the list box. eg: If Country is selected from the combobox then the list box should contain all the names of the countries from the country Column 2)An item should be added to the list in the worksheet when an item is entered in the textbox. Like wise, when an item is selected from the combobox from "Delete Items List" all the items relating to the item selected from the combobox should be displayed in the list box and a choice to delete the items relating to the combobox item should be provided.
View Replies!
View Related
Changing Range Of Sumproduct Function Depending On Length Of Data Series
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View Replies!
View Related
Assign Value To Text Formatted Cell
I want to be able to "count" apples and oranges. Is there a way to record a particular text in a text formatted cell and count it. e.g. 10 cells, 3 say "apples", three say "oranges", 4 say "plums". I want excel to keep track of the three types of fruit when I change them and give me a running number of each.
View Replies!
View Related
Assign Value From Closed Workbook Cell
Is it possible to write a macro in Book1 to assign Public Varibles in Book1 to values located in cells in Book2 without first opening Book2. So lets say in Book2, sheet1, cell B2 contains the number 5. I want Public Variable "Number_value" in Book1 to equal the number 5.
View Replies!
View Related
Assign Data To A Separate Cell Based On Value Of Yet Another
I have a "memo of meeting" excel document used to capture notes during client meetings. In this document there's a cell for "purpose", "agenda", and "notes". The 'consultation type' cell is a drop-down populated by values set in Sheet2. The 'agenda' & 'notes" cells are then populated with information (also stored in Sheet2) based on what drop-down option was chosen in the 'purpose' cell. I'm currently making this happen with basic =IF() statements; however, the problem is that by doing it this way, the =IF() statement must reside in the actual cell which will end up showing the data. This is undesirable as there may be additional notes, agenda items, etc. that I'll need to add during the meeting and as it's currently setup, I can only have either the pre-populated info OR the ad-hoc notes - not both. It seems a simple fix. Maybe put the =IF() statement in a cell off to the side but have it "assign" the value to 'agenda'/'notes' but that doesn't seem to work.
View Replies!
View Related
|
TRACKER
