Calculating grades using a number inside a text string using VBA
Calculating grades using a number inside a text string, using VBA or Formulas.
I am using Excel 2010.
On Sheet2 (of the attached file), in Columns G and H, I have a formula using IF-THEN-ELSE to give a Grade level based on the score entered in columns K and L. The formula works perfectly as long as in columns K and L it is entered a number.
However, my actual scenario is a bit different.
If you look at Sheet1, in columns K and L, there is a string of text in each column where you can find a three digit number called Scale Score.
I need to apply the formula from Sheet2 to that three digit number in the text string.
I tried using MID (to extract the three digit number) and IF in the same formula, but I can’t make it work. The result must go in columns G and H respectively.
I am using this scenario inside a table (called “Classlog”) where the data starts at row 4 and it has to be applied to the entire column(s) in the table. The solution (formula or VBA) should not work outside the table and it should start in row 4). The table will grow as data is entered so the solution must account for that too.
I hope it can be done with a formula or VBA. If possible, the solution should not use helper columns (since it would be outside the table and the actual workbook has other data all over the place). Also, if the cells are empty in columns K and L, then the solution should also give a “blank” or empty result.
I prefer VBA but if both options can be provided much better. If using VBA, can it be done so that it automatically monitors columns K and L and it enters or deletes the result in columns G and H automatically – just as a formula would do?
NOTE: The actual workbook is already using VBA code for the “Worksheet_Change…” and the “Worksheet_SelectionChange…” Subs, but the code allows for more code to be added as long as it is “self” contained, you know, that it does not end the sub. The attached workbook does not have any code, it’s clean.
The Scale Score I am using is as follows (which should be able to be edited later):
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,914
Re: Calculating grades using a number inside a text string using VBA or Formulas
Where in the workbook have you mocked up what you want?
To turn text into a real number, you can do this:
=--MID(K4,13,3)
Really not sure what you are asking for help with here.
For grading, you need to set up a lookup table in ascending order and then use INDEX MATCH or VLOOKUP to convert the score to a gradei nstead of nested IFs.
Last edited by AliGW; 10-31-2021 at 05:30 AM.
Ali Enthusiastic self-taught user of MS Excel who's always learning! Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish. Forum Rules (updated August 2023): please read them here.
Re: Calculating grades using a number inside a text string using VBA or Formulas
On Sheet1, in cell G4, the solution should render (for example, based on the three digit number inside the text string in cell K4): Grade Level 5
Also, in cell H4, it should give (based on the three digit number in cell L4): Grade Level K
And so on down the column.
Oh, by the way, I used nested IFs because unfortunately I cannot use a reference (lookup) table... it has to be inside the formula or the VBA code - all in one go, self contained.
Re: Calculating grades using a number inside a text string using VBA or Formulas
Well,
Sheet1 is waiting for a solution, what ever that is. Sheet2 is using the nested IFs and they work flawlessly but only if a number is entered.
Sorry, you are way too fast... I was editing this reply when you had already answered and I only saw the formula you gave after I posted it. I tried your suggestion and I think it work. Give me some time to make sure.
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,914
Re: Calculating grades using a number inside a text string using VBA or Formulas
No, but why don't you just protect the cells containing the formulae? It's easy enough to do.
Just format any cells that need editing as UNLOCKED on the Format ... | Protection tab on the right-click menu, then go to Review | Protect Sheet. You can set a password if you want to. This would prevent your formulae being deleted accidentally - someone would have to deliberately override this setting to do any damage.
Please don't use Ms - I hate it, and it's not my title. Just use my forum user name. Thanks.
Re: Calculating grades using a number inside a text string using VBA or Formulas
Sorry about the Ms. In the area where I live it's the only proper and respectful title to use in a neutral way. Although I am not originally from this country either - but I have to adapt to what they use here.
Yes, I know about locking the sheet and even the code area... the problem with using that option is that then the table sorting will not work properly (the workbook is using INDERCT and other similar formulas that link to other sheets). We have tried but is best not to lock the sheet and whenever possible to use VBA instead since it is out of sight.
So... in your area, what is the proper way to address with one word in a neutral manner a lady, a single woman, or a married woman when you don't know which one is it? I'm curious... I'm planning to visit England next year and I don't want to end up in a gutter.
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,914
Re: Calculating grades using a number inside a text string using VBA or Formulas
My advice to you is don't even try. When someone introduces themselves to you, they will either give a first name or a title and surname: wait for that prompt. The country is currently grappling with personal pronouns and all the rest of it: frankly, it's a bit of a minefield.
Whether a lady is married or not is no more your business than whether a man is married (not you personally, but people in general). The problem with Ms, though, is the other connotations that often go with it. I'm not a feminist, and my title is and always has been Miss, however I don't share that in my profile here because it is none of anyone's business and isn't necessary here. We have user names and that is what you should use to address other members, and nothing else.
Plesae don't address my as anything other than Ali (in my signature) or AliGW. Anything else is inevitably presumptive. Thanks.
Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
MS-Off Ver
MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
Posts
80,914
Re: Calculating grades using a number inside a text string using VBA or Formulas
whenever possible to use VBA instead since it is out of sight
If this is the case, then don't ask for formulae at all - it's a waste of the membership's time given that it will never actually be used. I'm going to update your title.
Re: Calculating grades using a number inside a text string using VBA
Got it.
Here in Los Angeles the only respectful way is using Ms. Anything else is considered rude or ignorant - and is has nothing to do with being a feminist or not - And yes, it's no bodies business if you are marred or not. Here, the "Ms." is used specifically to be neutral and not to intrude in that private area of someone life, so that no one can tell if you are single, married, widow, etc. I wonder what they use in Australia now... hummm...
For example, when I address my wife at work (we work at the same place) I have to call her Ms. so and so. When I address my teenage daughter I also use the Ms. and her last name. Here is it the only title to use to show that you are not intruding.
Anyhow, I meant no disrespect. After all, I have never been to England... but we are planning to go next year. Thanks for the heads up.
Thanks for your help AliGW. I'll go to sleep now since it's 3:23 am for me.
Oh, by the way... the title of this post does say "by VBA or Formula" and I did come into the VBA section of the forum.
Re: Calculating grades using a number inside a text string using VBA
I never waste any code... I use the formulas with the advance users we have. But for those other user who are just learning, that are on training, I rather use the VBA. For example, the workbook I use I rather use the formulas. My wife does not understand formulas and she simply wants to enter data - so her workbook will use the VBA.
Re: Calculating grades using a number inside a text string using VBA
I felt the same way when I arrived here... the language it's too... dry, I think is the word. I've been to worst places in the US than LA for sure. The one thing that attracts me about LA is that right or wrong, no one cares, as long as you "live and let live" sort of speak. But yeah, the proper language usage is lacking. If you overlook that for a second then the rest is really great. My wife is from Europe and yes, it took her a long time to "learn" the language used here - she is always correcting me since English is not my first language.
Re: Calculating grades using a number inside a text string using VBA
Question: If instead of extracting a number from the cell [using the --MID(K4,13,3)] I simply want to reference to the whole cell (and the cell will only have numbers now), how do you write the formula you provided? Yes, I like that formula. As you said, its much easier to maintain.
Silly me I tried simply removing the "--MID(K4,13,3)" and leaving the rest but obviously it did not work.
Re: Calculating grades using a number inside a text string using VBA
Yes, you got it.
How do you make this new formula to skip blank cells? Before it was leaving the target cells blanks if the source cells had nothing. Now it is writing "Grade Level K" in all the blank cells. Everything else works perfectly.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Re: Calculating grades using a number inside a text string using VBA
Awesome. Thank you.
Also, I am working on a VBA solution and when I'm done I'll post it here. It will take me some time since I'm learning as I'm going. For now, I'll mark as SOLVED this post.
But now with the passing of time we have found a new necessity:
Instead of providing whole numbers {"No score","K",1,2,3,4,5,6,7,8,9,10,11,12} now we need it with decimals (the result on screen): 1.2 or 1.5 or 1.9 etc. for each level respectively. How can this be done? Is this possible?
In other words, as it stands, it works based of this chart:
But now, we need the result to show "Reading Level: 1.2", or "Reading Level: 1.5", or "Reading Level: 1.7", etc., depending of what score was actually entered between 194 and 203 for Grade Level 1 (and the same for the other levels respectively).
For example (just an example, not the actual accurate result), if the student got a score of 201 then the grade result will be 1.9 and so on.
I hope I'm making sense.
I am hoping that it is a simple tweak to the formula being used (which works flawlessly and we want to keep on using it if possible - we use it within a vba macro). Perhaps the section &LOOKUP(--MID(I6,1,3) has a specific way to add a code here that will allow the grade to be shown with decimals, or maybe at the end of the formula }),"") there is a code like 0.0 or 0,0 or something that could be added to make it show results in decimals. I have experimented with this but no luck. For example with =FIXED(K4) or =FIXED(K4, 1) or ROUND, etc, meaning, K4 is the column where we use the formula, where we need the results in decimals.
If this is not possible with the current solution already found, can you please point me in the right direction for another formula (another post maybe) that can do what is needed (we will be using that formula with a macro).
Re: Calculating grades using a number inside a text string using VBA
Please see attached file. It is the actual file (minus the real data, I cleaned it).
Everything works with the file, except that now on columns K and L we need to show the results with decimal places.
The VBA we are using is a RESET macro which injects all the formulas in the table. Some of our staff sometimes clears the formulas by mistake so there is a button (link to the vba) that they click and it will reset/put back all the formulas, so that part is fine. I also remove that macro and button from the attached file so not to create confusion.
We just need the formula to show decimals in column K and L.
Re: Calculating grades using a number inside a text string using VBA
Yes, that's exactly what we need.
However, I change the score in column M from 221 to any other number and the result in column K reverts to whole number, it looses the decimal.
I tried to see what changes you did to the formulas in columns K and L and I can't figure out how you managed the decimals in those two cells highlighted in yellow.
Re: Calculating grades using a number inside a text string using VBA
OK. If you understand how LOOKUP works, its easy to fix.
=LOOKUP(value, lookup_range, result_range)
If lookup_value = 221
with lookup_range:
{0,1,194,204,211,217,223,228,231,235,239,244,249,254}
lookup_value (221) is found in position #6 (>=217)
then with result_range:
{"No score","K",1.1,2.1,3.3,4.5,5.6,6.2,7.1,8.5,9.1,10.1,11.1,12.9}
the result should be 4.5 (same position #6)
Re: Calculating grades using a number inside a text string using VBA
Ah... I found my mistake. I copy/pasted the solution you provided but it was pointing to the wrong row so I had to modify it to point to it's corresponding row. I will test this more and let you know in a bit.
Re: Calculating grades using a number inside a text string using VBA
The numbers do not match the expected output score. Yes, now they are decimal, but the result is not accurate, they are a bit off...
For example, in the file provided, if in cell M4, you change the score to 227 then the calculated score displayed in column K4 should read: "Reading Level 5.9" but instead reads 5.6
I was able to dig some accurate scale results (these are real results this time) so that you can see what I mean. Enter in cell M4 which ever one of these values and it should give you in cell K4 that exact level:
In M4: Scale Score=227 --- and K4 should be: Reading Level: 5.9
In M4: Scale Score=230 --- and K4 should be: Reading Level: 6.9
In M4: Scale Score=225 --- and K4 should be: Reading Level: 5.5
In M4: Scale Score=221 --- and K4 should be: Reading Level: 4.7
In M4: Scale Score=247 --- and K4 should be: Reading Level: 10.7
In M4: Scale Score=233 --- and K4 should be: Reading Level: 7.7
In M4: Scale Score=219 --- and K4 should be: Reading Level: 4.4
Unfortunately, these are the only real scores I managed to dig at the moment, but it should give the correct idea of the decimal output expected (and the calculation of the scale range).
.
.
.
In other words (according to the scoring table), the formula that we are using now always gives "Grade Level 4" if the Scale Score is between 217 and 222. It is "Level 4" for any score between 217 and 222.
But what we need now is to show that:
If the Scale Score is 219 then the Level is 4.4
If the Scale Score is 221 then the Level is 4.7
And the formula will display the correct level for all the scores entered under Level 4:
Scale Score range: 217-222 for LEVEL 4
If the Scale Score is 217 then the Level is 4.?
If the Scale Score is 218 then the Level is 4.?
If the Scale Score is 219 then the Level is 4.4
If the Scale Score is 220 then the Level is 4.?
If the Scale Score is 221 then the Level is 4.7
If the Scale Score is 222 then the Level is 4.?
Scale Score range: 223-227 for LEVEL 5
If the Scale Score is 223 then the Level is 5.?
If the Scale Score is 224 then the Level is 5.?
If the Scale Score is 225 then the Level is 5.5
If the Scale Score is 226 then the Level is 5.?
If the Scale Score is 227 then the Level is 5.9
Usually in Excel, you right click on a cell (or a range), then click on "Format Cell", then in the "Number" tab and inside the "Category" you simply click on Number and set it for two decimals, etc., and since the formula is already doing the calculations then is just a matter of adding the decimals. But in this particular case, the cell contains text and number so that usual way does not work here.
Re: Calculating grades using a number inside a text string using VBA
With formula, it required complex formula combination, which is available in Ex 360, which version both of us do not have.
For Ex2016 (me) or 2010 (you), it is very complicated formula to do it!
I tried with VBA first.
With raw data in column M & N down, results are in K & L
How to calculate:
with score: 221. It belongs to 217-223 range, with score = 4
result = 4 + (221-217)/(223-217) = 4 + 0.67 = 4.67
try below code:
PHP Code:
Option Explicit Sub LevelM() Dim lr&, i&, j&, k&, rng, res() Dim Score&, Sc, Lv, Level, Low, High lr = Cells(Rows.Count, "M").End(xlUp).Row ' last used row rng = Range("M4:N" & lr).Value ' data value array ReDim res(1 To UBound(rng), 1 To 2) ' result array Sc = Array(194, 204, 211, 217, 223, 228, 231, 235, 239, 244, 249, 254) ' array of score Lv = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) ' array of Level For i = 1 To UBound(rng) For j = 1 To 2 If rng(i, j) <> "" Then On Error Resume Next Score = CLng(Trim(Split(Split(rng(i, j), "=")(1), "/")(0))) ' number between first "=" and first "/" Select Case Score Case Is < 194 Level = "K" ' lowest level Case Is > 254 Level = 12 ' highest level Case Else k = -1 Do ' loop score within Sc array, until getting higher value k = k + 1 If Score > Sc(k) Then ' for example: score = 221 Low = Sc(k) ' lower value (for ex =217) Level = Lv(k) ' matching level value (for ex =4) End If Loop Until Score <= Sc(k) High = Sc(k) 'higher value (for ex =223) Level = Format(Level + (Score - Low) / (High - Low), "0.00") ' = 4 + (221-217)/(223-217) = 4.67 End Select res(i, j) = IIf(j = 1, "Reading ", "Math ") & "Level: " & Level On Error GoTo 0 End If Next Next Range("K4").Resize(UBound(res), 2).Value = res End Sub
Re: Calculating grades using a number inside a text string using VBA
bebo021999
Yes, the vba is working fine so far. I will modify our sheet to use it. It's a sheet with a lot of data and many macros already. Thank you. If you can think of a formula do let me know please, since it will be easy to just replace it for the one we have now and the structure of our sheet can remain the same.
Re: Calculating grades using a number inside a text string using VBA
Yes, I though of that too, but the only UDF formula I came up with requires to know each .x level for each score (instead of a score range). So far, I have not been able to figure out how to make the formula to auto-calculate the .x according to the score entered.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks