I'm putting together a mark book for school and have a problem with VLOOKUP distinguishing between what used to be a top grade, ie A, and the new top grade, A*.
I want to convert the grade to a number but VLOOKUP can't tell the difference between A and A*. I've attached a sample worksheet.
I have a range of cells C11:C29,G12:G18,G20:G23,G25:G26,G28:G29,C33:C42,G33:G42,C46:C47,G46:G47,C51:C54,G51:G59,C58:C59 that contains letter grades (egs. A+,C- etc). I need the code for a macro that checks each cell from this range and if the value of the cell is A+ then the cell's value changes to 100, if the cell's value is A then change the cell's value to 98, if cell's value is B+ then change to 88 ...and so on...you get the idea (i will complete the rest of conditions but i just need the basic code).
View 6 Replies View RelatedI am looking for a function that would convert any mark into a grade. For example in cell B4 I want to enter the total score, cell c4 the score that a student gets and in cell d4 the grade. It is like a grade calculator. I do not want to limit cell b4 the total score cell with a specific total score number. It can be 100, 50, 30 or 10. The moment I enter any total score and the mark that a student actually scored, the grade is given. I attached an excel sheet.
Marks and Grades Calculator.xlsx‎
I am compiling a master spreadsheet to analyse progress of students. I have their potential grades in one column and their latest progress grade in another.
I would like an automatic look up of the potential to the progress grade so that if their current grade is BELOW their potential that gets highlighted one colour, if it is above, another colour.
One issue is that the grades are all letters (S, A, B, C, etc.) and that some potential grades are 'dual' (i.e. A/B, S/A) which complicates the issue somewhat.
my Excel teacher doesn't even know how to do this! The chapter is Working with Multiple Worksheets and Workbooks, and we went into linking workbooks, linking worksheets, using the VLOOKUP function, and saving workbooks as templates. This is Case Problem 2 and i'll attaching the file.
It says:
1. On the Grading Criteria worksheet, enter the text "Exam Average" in cell A1
and the text "Grade" in cell B1.
2. In the range A2:B6, create a lookup table for the range of grades specified by Professor Templeton. (Hint: Each letter grade should be matched up with the lowest score possible for that grade.)
Here are the specified grades:
0 to 49 = F
50 to 59 = D
60 to 74 = C
75 to 89 = B
90 to 100 = A
3. Insert a formula in Column E of each worksheet to calculate the overall score of each student based on the grade table you entered in the Grading Criteria worksheet.
I have figured out how to assign a letter grade to a number, but am having trouble assigning it the other way, a number to a letter grade. For instance: If a student gets an A, I want the column next to it to indicate that the A represents a 4; a B represents a 3; a C represents a 2; D a 1; and F a 0. This will allow an easy grade point average calculation.
A 4 History
C 2 Math
A 4 English
B 3 Physical Ed
D 1 Science
GPA 2.80
In a worksheet of marks of students, i have entered grades A,B,C,D,AND E.Grades are entered in cells o3,AB3,AO3,BB3 AND BO3.
In BQ3,I want to get -in the range of O3:BO3
a)how many "A" are there?
It should display for example A=2,
b) how many "B" are there?
It should display for example B=2,
c)how many "C" are there?
It should display for example C=2,
d)how many "D" are there?
It should display for example D=2,
e)how many "E" are there?
It should display for example E=2.
In BR3, I want to get >
If A=10, B=8, C=6, D=4, E=2 then
display the total value for the grade letters.
Pls see the attached file for more clarity.
This is probably easier than I'm making it on myself. I would like a formula that would find the value in D2 of the current sheet on a sheet named 6-19 in D:D, then take the value from L:L in that row and subtract it from L2 of the current sheet.
I've attached an example workbook : Cost Calc Upload.xlsx‎
I am trying to compare two lists and extract from one to add back to the source. However neither are alphabetically.
List 1
accessories
belt
boots
cardigan
coat
glasses
gloves
hat
List 2
tshirt
tshirt
jumper
jumper
jumper
tshirt
shoes
cardigan
So i would the vlookup to add automatically back to list 1 tshirt, jumper and shoes.
List 1 would now look like this
accessories
belt
boots
cardigan
coat
glasses
gloves
hat
tshirt
jumper
shoes
I want to determine the days' difference between 2 days, but I keep getting decimals.
(I don't want to foramt the column, because I want to use the value in a Pivot Table.
I've tried:
=SUM(TODAY(),0-G2,0)
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
[URL]
I'm trying to get an excel custom number format to show the difference in two dates as 0 years and 0 months...for e.g.
Cell a1 = commencement date = 1/1/05
cell a2 = expiration date = 12/31/10
Then cell a3 = Lease Term = 6 years 0 months.
Does clipboard method gettext retreive the text from clipboard only, not number? What if numbers are copied (Ctrl C) to clipboard?
View 9 Replies View RelatedHow could I convert this 'IF' into a VLOOKUP function? =IF(H20 is inbetween 12-14),1,IF(H20 is inbetween 15-17),2,IF(H20 is inbetween 18-19),3,IF(H20 is inbetween 20-21),4,IF(H20 is inbetween 22-23),5,IF(H20 is inbetween 24-25),6,IF(H20 is inbetween 26-27),7,IF(H20 is inbetween 28-30),8,IF(H20 is inbetween 31-32),9,IF(H20>33),10,"")
I know "is in between" is not part of any excel function, just wanted to spell it out for clarity of what it is I want to do.
Im trying to convert the below code into VBA:
=VLOOKUP(CONCATENATE(A1,B1,C1),F1:Y4,20,0)
AreaOwner, Project_Name, ProjectCode are all defined variables
Sheets(A).Cells(6, 3).formula = VLookup(CONCATENATE(AreaOwner, Project_Name, ProjectCode), Sheets(b).Range("F1:Y10000"), 5, 0)
Sheet1 is Main Sheet which never will be amended and these numbers will be a source for pulling mathematical functions
See attached :
Sheet1.png
Don't be bothered about cell colors etc. They are for my reference
Sheet2
Will contain same Problem Ticket numbers but different (or same) values with number of 'Linked Incidents'
See screenshot
Sheet2.png
Based on Problem ID 10248
Main sheet shows 92 Linked Incidents
Value on Sheet two brings 93
If A5 Sheet2 value can be found in ColumnA Sheet1, look for a number in ColumnC, same ROW and compare it to C7 in Sheet2 ?
Bring up difference value, which in this case would be one.
i have this function that poulates data from a userform into a sheet.
For lLoop = 1 To MAWBNoVar
With Range("B" & Rows.Count).End(xlUp)
.Offset(1, 0) = Controls("txtMAWB" & lLoop)
.Offset(1, 1) = Left(Controls("cbDestination" & lLoop), 3)
.Offset(1, 2) = Controls("txtGW" & lLoop)
.Offset(1, 3) = Controls("txtCBM" & lLoop)
.Offset(1, 4) = "= ROUND(E" & Y & "*167,0)"
.Offset(1, 5) = "=IF(D" & Y & ">F" & Y & ",D" & Y & ",F" & Y & ")"
.Offset(1, 6) = "=IF(F" & Y & ">D" & Y & ",G" & Y & "-D" & Y & ",G" & Y & "-F" & Y & ")"
Y = Y + 1
End With
Next lLoop
My problem is that the data being entered into the sheet for the lines highlighted in red is going in as text, and not true numerics, so that the subsequent formulae are not working correctly.
I had someone look up a price on a website, for a list of part numbers, and copy the link to the website page into my spreadsheet.
Now I want to transfer the price and link to my main spreadsheet.
I know how to use VLookup to get the info to my main spreadsheet but when I transfer the link using VLookup it does not come as a hyperlink that I can click on and be sent to the webpage. It is just text.
How do I convert the links to a hyperlink that I can click on and be sent to the webpage?
I have 1200 records.
HTML Code: VLOOKUP(M2,'FX Rates'!$A$1:$P$199,2,FALSE)
I have lots of vlookups in my spreadsheet. Match and Index is a better method and should speed up my spreadsheet (1000s of vlookups!).
I'm just trying to get a total of columns E, F and G, and place those totals in row 5. The problem is that somehow the numbers in column G were stored as text, so the SUM formula is coming up "0". How would I format these cells, in a macro, (columns E, F and G, just in case the data in the other two columns were stored as text), as a number so that I can total them.
I've tried selecting the column, selecting format cells and choosing number, but that doesn't work. I can manually select the cells, click the little exclamation point, and then tell it to convert to number. This works, but when I tried to record it to add it to the macro, it didn't record anything but the selection of the cells.
I have a string of imported data which comes into an existing spreadsheet. It cannot be changed prior to importing as the data is provided by an external vendor.
When the data is imported into my spreadsheet the Error Checking tag comes on, after I select the cells where certain numeric data has been entered, stating that this is a range of Numbers Stored as Text. If I click on the Error Checking tag and select the Convert to Number option and then enter my VLOOKUP function I get the desired answer. If I do not convert the text to numbers then my VLOOKUP function does not work at all. The cell just displays the function text itself instead of the desired answer.
Here is what I am requesting:
Assistance with writing the VBA code to accomplish the manual task of converting the text to numbers. I have tried to format using the Ribbon without success.
when i import some data from web
some of the character show ¾ or 1½ ..these look like a word rather than number
so may i know how can i convert "1½" to 1.5?
I have a combobox embedded into an excel sheet with a linked cell and a range defined in the combobox properties. Every time I change the value in the combobox, the value in the linked cell is stored as text instead of a number.
I have tried copying a cell containing 1 and copying and pasting as special into the linked cell but that only fixes it for the time being. If I change the value in the combobox again, the value in the linked cell is again stored as text. I tried =value() in the linked cell but that again only worked temporarily. Every time I change the value in the combobox, the value in the linked cell is stored as text.
I have a date in a particular cell, say O5, formatted as mm/dd; e.g. 11/14
I have the following formula in another cell;
=M5 &", Lot# "& N5 &", Expires "& O5, where cell M5 = "CONTROL" and cell N5 = "#####"
Why does the date get converted to a serial number. i.e. CONTROL, Lot# #####, Expires 41592?
My aim is to get the date "11/14" to appear in the cell.
I am trying to convert the name of a month to a number but there is a slight issue with using my normal method of TEXT("1/"&A1&"/2013","mmm")
I am trying to create a dynamic table which if you change 1 value for the month, it changes others
so my data looks like so,
Jan
Feb
Mar
Apr
May <---- this is the value which is chosen
The problem is when I select the month "January", it assigns the value of the above cell as 1/0/2013 which is not a date when in fact I want to set it as "December".
Here how to convert Number to Text..Like 100 means One Hundred.For this any formula is there? i want to change Full A Column numbers to Text..Number To Text.jpg
View 2 Replies View RelatedI exported a report from Crystal to Excel. The times came over as numbers. How do I convert the numbers into time.
Example: 753 to 7:53
1426 to 14:26
Is there simple function that anyone knows of (or has written) that will convert a letter to its alpha-numeric equivalent?
For instance, A = 1, B = 2, AA = 27, etc (a = 1, b = 2, aa = 27)
i have number in database 1 and want to convert as date in database 2
04079219920506
04089219920507
04099219920510
18730120010729
18740120010801
18750120010804
18760120010805
result in database 2
407/9206/05/1992
408/9207/05/1992
408/9210/05/1992
1873/0129/07/2001
1874/0101/08/2001
1875/0104/08/2001
1876/0105/08/2001
is it got any formula to convert this number from database 1 to database 2, so far i has attach sample files..
Need to convert number to identical quantity of spaces. Example:
A1 is 5
B1 should consist 5 spaces (or other characters).