2 variable function in a formula

Hello!

I have 2 variables that determine what the returning value is. First variable would be Name (row), second variable would be Score (column header). The Score value in the formula is a variable and is determined by data entry. For instance, if the score@row value is 99 it needs to find the column 99, if the score@row value is 72 it needs to find the column 72 and bring back the appropriate letter grade for each instance for that schoolname@row (Name).

I've tried to do INDEX MATCH and IF(CONTAINS), but can't get the column range reference to be a variable as determined by the value found in the field Score@row.

Score 99 98 97 96 72

Name A A A A C

I have multiple schools with different grading scales and need to find the letter grade based on the school name and have the letter grade value returned for the course score. My primary goal is to have one transfer credit processing sheet that will reference the grading scale sheet and bring back the right letter grade for data upload.

Previously, I was using one Excel file per school to create the upload data with a grading scale maintained on a tab. I was using Excel VLookUP functions to bring back the value, but would like to integrate it onto Smartsheet as one sheet with all schools listed (easier to reference and to maintain).

Any ideas on how to get this to return the right information? Do I need to make separate sheets for each school (similar to how I was in Excel) or can I combine it like I'm trying to do? Is there a better way to set this up?

Find more posts tagged with

Accepted answers

All comments

Devin Lee

Hey @ZennerJ

Grade Scale for Schools

Create a Sheet with the Column "Grade" followed by more columns for each school. In the Grade column use the highest possible score to the lowest (100 to 0). In each school column type in the grade that corresponds to each number.

Finding the Right Column

In a second sheet make columns for School, School Column, Grade, and Letter Grade. The trick here is to use the School to identify the column that will be looked at using the formula below. (Replace the "School A,B,C,D, etc" with the correct school name)

=IF([School1]@row = "School A", 2, IF([School1]@row = "School B", 3, IF([School1]@row = "School C", 4, IF([School1]@row = "School D", 5, "School Missing"))))

VLOOKUP the Matching Letter Grade

Then in the Letter Grade column you can use the VLOOKUP formula:

=VLOOKUP([Grade1]@row, {Reference 1}, [School Column]@row, 0)

Reference 1 being the whole first sheet. It would look like this if they were on the same sheet

=VLOOKUP([Grade1]@row, Grade:[School D], [School Column]@row, 0)

End Notes

Keep in mind if you add more schools to the first sheet they need to be at the end and you will need to add those options into the School Column function. I added in an image and drew a yellow line to separate the two sheets. Hopefully this helps.

ZennerJ

Actually, that was our first thought on how to do this, but I'm working with over 50 international schools from one country with about 15-20 different grading scales and another 30 from other countries with 6-8 grading scales. (And thank you! I forgot you can put the column header names within a formula!) We are working to create the system by country and see if that will work before integrating it all together.

Right now, my team and I are entering and uploading around 12K lines of transfer credits per month for our high school students thru the excel processing (one file/school), and I need to get it more automated since the new schools being added in the next 3 months are large enough that we are estimating the volume will more than double before the end of next year.

My hope is to have one translation sheet to maintain, one transfer credit sheet to maintain, and one grading scale sheet to maintain with the data entry sheet reference the appropriate sheet to create the final data entry to upload into our SIS.

Having a IF statement with 50+ schools referenced is asking for formula errors as the number of schools grow. I also have an issue that a few of the schools have different grading scales for different years (prior to 2020 vs after 2020) type of situation, AND a few that have different grading scales for 9th vs 10-12th grades. I have to use the right scale based on that information, too. I've got a JOIN field in the sheet to indicate the correct school/year/grade. Is there any way to use a JOIN field to help find the reference of the column name without all those sub-formulas within the IF statement?

[Screenshot of Data Entry sheet]

I am using Index/Match formulas to pull the English translated course name and credit with the @row function. This is working despite what the screenshot shows. I just need to figure out how to pull the right letter grade based on the score and appropriate grading scale to complete the process.

I currently have a column showing a reference for the grading scale (similar to the "school column" referenced on your example) on the Transfer Credit sheet to help find the correct grading scale reference for the letter grade conversion, but that would be 40-60 entries per school per year per grade that I would have to manually maintain. To help segregate the information, I was hoping to have one sheet with all the grading scales referenced so I only have one place to maintain that information rather than integrate it within the transfer credit sheet.

[Screenshot of Transfer Credit sheet]

[Original Grading Scale sheets]

I have 6 grading scale sheets to date [50, 60, 70, Custom-1, Custom-2, Custom-3]; I could add the school names at the column headers and just segregate based on the scale type, but that leaves the year and grade function missing unless I do one column for each of those as well.

[Proposed Grading Scale Sheet]

Another option, is that I can (but would prefer not to) have a sheet per grading scale and reference which sheet to look at based on the grading scale referenced for that school/year/grade on the Transfer Credit sheet. Would this be the better solution to not have so many references within the IF statement? As indicated above, I already have 6 grading scale sheets made up trying to figure out the best way to get the formulas to work for this one aspect.

Let me know if you still believe the IF statement function you proposed is the best solution given the new information. Thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.