Turning non-numerical teams names into numerical values

[Badfish54]

The school I work for is crafting a system where students have to do a sport, and depending on which sport they do they will earn a numerical value for that sport. Freshman would need 6pts, Sophomores ~4, Juniors and Seniors 3.

I am looking at a way to input information into excel to keep track of the information, but to do it so the following information would show:
Varsity Football = 2pts
JV Football = 1pt
Varsity Soccer = 2pts
JV Soccer = 1pt

and so on with Varsity sports as 2pts, and JV as 1.

Is there a way to have excel read what their fall, winter, spring choices are, compute the related numbers, and then give a sum? Is it still possible if I were to put a locked drop down menu in the cells to select the sport?

Thank you

 

[ButtFace]

Something like this where column A contains the dropdown menus?
=IF(FIND("VARSITY",upper(A1)),2,IF(FIND("JV",upper(A1)),1,0))

 

[Badfish54]

thank you for the help. So it works with varsity, however if I put JV in then it shows an error. And that is testing without the dropdown menus.

 

[Nishant94]

Assuming the Dropdown to be in column A,

=IF(ISNUMBER(SEARCH("varsity",$A1,1)),2,IF(ISNUMBER(SEARCH("jv",$A1,1)),1,0))

To see for example "2 pts" instead of just 2, change the formatting of the cell.

Select the Column B where you are expecting your answer, press ctrl+1, go to custom and type this: 0 "pts";0;;

Now you can do the sum of column B easily

 

[Badfish54]

That works perfectly!!!

Thank you.

My next question is is there a way, considering columns A, B, C will be Fall, Winter, Spring sports respectively, show there sum of the sport value in column D? Would i just be able to take that formula, place it in a cell in Column D, change the target cells accordingly, and plus + between the three instances of it?

 

[Nishant94]

That works perfectly!!!

Thank you.

My next question is is there a way, considering columns A, B, C will be Fall, Winter, Spring sports respectively, show there sum of the sport value in column D? Would i just be able to take that formula, place it in a cell in Column D, change the target cells accordingly, and plus + between the three instances of it?

If you can give a sample of data, it will help us in properly understanding what you exactly want.

Thanks.

 

[Badfish54]

My mistake. Sorry

However I just figured it out by placing a "=SUM(" before the formula, adding commas, and a ) at the end.

My last question, and I promise this will be, is if I were to require my Freshman students to obtain 6pts worth of sport credit, is there a way in either the =sum total field or next to it have it check that if it is 6pts or more then it turns a certain color, and if under that amount then its another color. And it checks a students grade level?

Example:

Name Grade Fall Winter Spring Total Points Met Points
John 9 JV Football JV Boys Basketball Varsity Lacrosse 4 ??????

Where 9th graders need 6pts

 

[Nishant94]

My mistake. Sorry

However I just figured it out by placing a "=SUM(" before the formula, adding commas, and a ) at the end.

My last question, and I promise this will be, is if I were to require my Freshman students to obtain 6pts worth of sport credit, is there a way in either the =sum total field or next to it have it check that if it is 6pts or more then it turns a certain color, and if under that amount then its another color. And it checks a students grade level?

Example:

Name Grade Fall Winter Spring Total Points Met Points
John 9 JV Football JV Boys Basketball Varsity Lacrosse 4 ??????

Where 9th graders need 6pts

Expecting your example table given above is from A1:G2 , and your sports event in column C to E and the met point is in column G, then put this formula in G2:

=SUM(IF(ISNUMBER(SEARCH("varsity",$C2:$E2,1)),2,IF(ISNUMBER(SEARCH("jv",$C2:$E2,1)),1,0)))


And regarding color in case of value above 6 you have to do a conditional formatting, steps:

1) Select the cell G2:G1000 (assuming the maximum no. of record is 1000) and make sure the cell G2 is the active cell(G2 will be white in color). (hint: While selecting the cell start from G2)

2) After selecting, press alt+O+D, then press alt+n, then select "Use a formula to determine which cells to format"

3) Then type this formula in the box just below "Format value where this formula is true":

=G2>=6

4) Then select Format...

5) Go to Fill tab and then select a color and Click "Ok" and the press "Ok" again.

And you are done.


--------------) if you want color for value less than 6 then follow the same procedure but change the formula to

=G2<6


Hope that helps.

 

[Nishant94]

Expecting your example table given above is from A1:G2 , and your sports event in column C to E and the met point is in column G, then put this formula in G2:

=SUM(IF(ISNUMBER(SEARCH("varsity",$C2:$E2,1)),2,IF(ISNUMBER(SEARCH("jv",$C2:$E2,1)),1,0)))


And regarding color in case of value above 6 you have to do a conditional formatting, steps:

1) Select the cell G2:G1000 (assuming the maximum no. of record is 1000) and make sure the cell G2 is the active cell(G2 will be white in color). (hint: While selecting the cell start from G2)

2) After selecting, press alt+O+D, then press alt+n, then select "Use a formula to determine which cells to format"

3) Then type this formula in the box just below "Format value where this formula is true":

=G2>=6

4) Then select Format...

5) Go to Fill tab and then select a color and Click "Ok" and the press "Ok" again.

And you are done.


--------------) if you want color for value less than 6 then follow the same procedure but change the formula to

=G2<6


Hope that helps.

Make sure you press Ctrl+shift+enter after putting the formula:


=SUM(IF(ISNUMBER(SEARCH("varsity",$C2:$E2,1)),2,IF(ISNUMBER(SEARCH("jv",$C2:$E2,1)),1,0)))

As this is an array formula it will work only if you press ctrl+shift+enter

Only pressing enter wont do the job.

 

[Badfish54]

I am forever in your debt. This is perfect.

I got everything in and tested it and it works perfectly. I showed it to my supervisor and she wanted to know if there is a way for to assign Freshman, Sophomore, Junior, Senior a value so that when the met value is calculated it can then check to make sure that the needed value is hit.

For example, if a student is a Sophomore, then they may only need a met value of 5. Is there a way to have it check Column B, which has their grade, determine that a Sophomore's met value needs to be a 5, and then flash a color in column G when it has met that value?

Thank you