Stupid Excel Tricks

Posted:
in General Discussion edited January 2014
I love it when I come up with a solution to an Excel problem. Not much else I can boast about these days, and I have to keep my self-esteem up somehow.



Anyway... I use Excel to calculate grades for my girlfriend's English class, among other things. I've got it to the point where for any new class, all I have to do is enter the raw homework and exam scores for each student and the percentage points and final grade out of a hundred are automatically calculated. But until now I had been entering the letter grades by hand: Less than 50=F, 50-59.9=D, 60-69.9=C, 70-79.9=B, 80 or above =A.



But I wanted to have Excel give me the letters so I wouldn't risk making an error.



It took me longer than I thought. I don't think Excel has a replace function that can work with arrays, i.e.

Code:


=REPLACEARRAY([0,1,2,3,4,5,6,7,8,9,10],[F,F,F,F,D,C,B,A,A,A])







If such a function existed, this would have been an easy task. But I don't think it does (correct me if I'm wrong).



So I had to come up with a workaround. I didn't want to add any new cells for calculation.



After about 15 minutes, I found the solution:



It looks like this:



Code:


=IF(N2<50,"F",CHAR(((8-TRUNC(N2/10)+ABS(8-TRUNC(N2/10)))/2)+65))







Where N2 is the grade percentage. The output is the letter grade.



This produces exactly the results I was looking for, and my grades are ready to be recorded.



Anyone care to analyze how it works?

Comments

  • Reply 1 of 15
    kickahakickaha Posts: 8,760member
    Pretty easy, a lookup into the ASCII character table based on the decile of the score.
  • Reply 2 of 15
    omegaomega Posts: 427member
    Why did you not just use nested if statements?



    In saying that Excel is arse and does not let you nest too many if statements - it chokes on 8 from memory.
  • Reply 3 of 15
    ast3r3xast3r3x Posts: 5,012member
    Quote:

    Originally posted by Omega

    Why did you not just use nested if statements?



    In saying that Excel is arse and does not let you nest too many if statements - it chokes on 8 from memory.




    Seemed to work fine for me when I had to take excel programming. (Office 2004)
  • Reply 4 of 15
    omegaomega Posts: 427member
    Quote:

    Originally posted by ast3r3x

    Seemed to work fine for me when I had to take excel programming. (Office 2004)



    I had to write a small formula for what tonton was looking for. Everything worked fine until I tried to set a grade for A+. I ended up having to split the formula in two to be able to complete the equation.



    Funny thing is I grew up on Lotus an can distinctly remember nested if's that encompassed 3-4 lines.
  • Reply 5 of 15
    ijerryijerry Posts: 615member
    Pretty nice. This is a cool little app for those wanting a GPA calculator. Just an FYI.
  • Reply 6 of 15
    andersanders Posts: 6,523member
    Excel is kewl
  • Reply 7 of 15
    splinemodelsplinemodel Posts: 7,311member
    Excel drives me nuts. I want a spreadsheet program that doesn't leave me with either a watered-down, math-only scripting language or an over-complicated thing called "VBA."



    What would be ideal is a spreadsheet built on top of Matlab. Pretty much all you'd need is a gui to do it, although it would be slow unless it received some special treatment.
  • Reply 8 of 15
    macchinemacchine Posts: 295member
    Quote:

    Originally posted by tonton



    Code:


    =REPLACEARRAY([0,1,2,3,4,5,6,7,8,9,10],[F,F,F,F,D,C,B,A,A,A])









    Code:


    =IF(N2<50,"F",CHAR(((8-TRUNC(N2/10)+ABS(8-TRUNC(N2/10)))/2)+65))













    Your solution is nice I like calculation based solutions like that but it is quite inflexible and would be difficult to maintain.



    Here is how you can create the solution in Excel very similar to what you suggested with your Replacearray() suggestion:



    So in column A:1-11 I type in the numbers 0-10.



    Then in column B:1-11 I type in the letters F-A, the same as shown in your list accept I add one F to the beginning because your example is one letter short.



    Then I do a lookup, in Cell D1, this is REAL easy to make the wizard guides you, ( choose the top value in the popup to do the lookup below ):



    =LOOKUP(C1,A1:A11,B1:B11)



    Or if you want to use VLOOKUP which according to MS is more modern and faster, you could use this:



    =VLOOKUP(C1,A1:B11,2)



    Or if you want to use HLOOKUP you could use the following with the number on top going across in row 1 and the grades in row 2.

    =HLOOKUP(C3,A1:K2,2)



    So my result is if I type a 9 into C1, D1 reads A.



    5 in C1, D1 it reads D.



    So now it all works as you would like and its arbitrary as you wanted just one value in one column looking up another value, whatever is in the column, next to it, or wherever that column is that you reference.



    So now lets make it more complicated:



    I change the As to add + and - , I put the minus in front just to make it HARD.

    Typical when I put the minus in front it tries to make an equation out it.

    So if you do this then its fixed: ="-A" you could also format the cell as TEXT but that can cause a lot of problems over time don't do it unless you have to.





    Then I make a list further down column A that reads as follows, my PTA approved list:



    Too dumb to live !!!

    Too dumb to read this.

    Super DUMB

    Extra DUMB

    Pretty DUMB

    TOTALLY lame

    kinda lame

    shorta good

    Pretty good.

    Don't you know teachers despise smart allexs like YOU !!!

    You are wild, crazy smart, Hoser Dood !!!





    And in E1 I create this equation: =LOOKUP(C1,A1:A11,A16:A26)



    So the list above is in A16:A26







    So if I type 0 in C1 I get in D1 and E1: FToo dumb to live !!!



    So if I type 1 in C1 I get in D1 and E1: FToo dumb to read this.



    So if I type 8 in C1 I get in D1 and E1: -APretty good.



    So if I type 10 in C1 I get in D1 and E1: A+You are wild, crazy smart, Hoser Dood !!!



    So if I type 9 in C1 I get in D1 and E1: ADon't you know teachers despise smart allecs like YOU !!!





    Don't feel bad I have been known for my Wild, Crazy, Excel madness for the last 20 years.



    I don't know all the tricks I just make my own.



    Now make the above with other equations HUGE nested if them statements until the spreadsheet is around 50 megs then you have the kind of spreadsheets I often build.









    OR IF YOU ARE TOTALLY WILD AND CRAZY GUY/GAL, YOU COULD DO THIS WAY___



    Cell F5:

    =IF(C1=0,B1,IF(C1=1,B2,IF(C1=2,B3,IF(C1=3,B4,IF(C1 =4,B5,IF(C1=5,B6,IF(C1=6,B7,IF(C1=7,B8,""))))))))



    Cell G5:

    =IF(C1=8,B9,IF(C1=9,B10,IF(C1=10,B11,"")))



    Make sure you put a black border around the above two cells because sometimes that give legitimate answers that are empty, so with the border you won't accidentally delete them.



    The grade result cell...

    =F5&G5



    Figure out those Apples !!!





    The most important thing programming in Excel can teach you is how to break away from nested, if thens, as needed.



    Excel is actually a get tool to accelerate your programming in C++, its great for prototyping algorithms !!!







    OH WELL, but then on the other hand if you are just LAZY you can always do it the EASY WAY !!!



    =IF(C1=0,B1,"") &IF(C1=1,B2,"") &IF(C1=2,B3,"") &IF(C1=3,B4,"") &IF(C1=4,B5,"") &IF(C1=5,B6,"") &IF(C1=6,B7,"") &IF(C1=7,B8,"") &IF(C1=8,B9,"") &IF(C1=9,B10,"") &IF(C1=10,B11,"")









    OR PERHAPS, this is the LAZEST way to do it, if you like bit twiddling this might be your favorite:



    =MID(C3,FIND(C1,C2),2) == -A



    C1: 8

    C2: ="0 1 2 3 4 5 6 7 8 9 10"

    C3: ="F F F F F D C B -AA A+"





    So the FIND is straight forward C1 is found in C2, it return a NUMBER the numerical position in the string.



    Then the MID goes to that numerical position in C3 and return 2 characters from that position.



    Now we get to the trick of this, I used spaces in C2 so that each position is 2 characters long, that way I could include the + and - in the grade string, the bottom line is C2 and C3 must have the same length of characters and that last number in the MID must have the same character length that you are using.



    In other words the last number in MID could be 4, but you would need to add 2 spaces to each position in your strings, C2 and C3, to make them twice as long in total.





    SO YOU CAN DO THINGS LIKE THIS:



    C4: ="Too dumb to live !!!To dumb t read this.Super DUMB Extra DUMB Pretty DUMB TOTALLY lame kinda lame shorta good Pretty good. Teachers Pet !!! Wild crazy smart !!!"





    And then your equation is:

    =MID(C3,FIND(C1,C2),2) & " " & MID(C4,FIND(C1,C2)*10-9,20)



    Notice I multiplied the position where the MID starts by 10, the factor of 2 into 20, subtracting 9 to get it to start on 1 instead of 10, anyway I could have made every string 20 characters per position then the first MID in the equation above would also have 20 in the last parameter. Its just easier to do it as it is.



    Which gives a result of...

    -A Pretty good.





    IF YOU WANT A VERY SIMPLE SOLUTION and will always use the 0-10 labels then you might like this...



    Use the index to directly access the grade string:

    =INDEX(B1:B11,C1+1,1)



    The grades are in B1 to B11 just like the top example.



    Nothing to explain this just works, the other examples might be easier to maintain in the long run.
  • Reply 9 of 15
    macchinemacchine Posts: 295member
    SEE !!! I answer this question, and no response at all.



    And then Apple posts a job THAT REQUIRES INDEX and VLOOKUP IN EXCEL !!!



    Nobody ever gets THAT specific in a job description...





    ... APPLE IS SPYING ON ME, NO QUESTION ABOUT IT !!!!!!





    Here it is...

    https://jobs.apple.com/cgi-bin/WebOb...3.29.3.0.1.1.1



    "Title: Sr. Planner/Execution Specialist

    Req. ID: 2304419

    Location: Sacramento, California

    Country: United States

    Req Date: 25-Apr-2005



    We have an opening for a Sr. Planner/Execution Specialist. We are looking for seasoned material professionals that are interested in progressing through additional managerial positions.

    In this position you will:

    -Apply expertise in demand planning, master planning, production planning, production control, inventory control, and logistics.

    -Analyze, develop, communicate and coordinate the total shipment volume forecast for a product family. Minimize forecast error by tracking and reviewing actual demand vs. quarterly/monthly/daily forecast, and influencing forecast changes to reflect projected demand patterns.

    -Develop, communicate and coordinate the master production schedule for a product line. Maximize DAILY billings and minimize finished goods liability. Maximize revenues and minimize finished goods liability by reviewing actual sales verses monthly forecast and recommending build changes.

    -Participate on new product team coordinating the build plans to ensure a smooth introduction. Act as liaison between Global Supply Mgmt and Sales Operations.

    -Provide direction to manufacturing plants. Interface with engineering to determine impact of engineering change orders (ECO) on MRP. Make recommendations regarding capacity and scheduling issues for new product introductions.

    -Create and manage PO?s to the supplier, based upon actual demand. Work with the supplier on PO and delivery reconciliation. Participates in selection and implements of supplier sourcing strategies to ensure high quality, on time delivery and cost competitiveness. Manages all aspects of daily vendor performance, including engineering revisions, on-time performance, receiving problems, and rejected material.

    -Coordinate effective inventory management, ensuring reduced cost and reduced risk of obsolescence for physical inventory, and lowers inventory handling cost. Implements strategies to achieve inventory objectives and build plans rationalizing flexibility in capacity, materials, capital and people.

    -Lead key initiatives and groups toward key performance measurements and continuous improvement activities.





    Requires BA/BS degree or equivalent, plus 5-12 years of experience. Requires strong Excel skills and the desired candidate must demonstrate indexing and v-look ups. Validation of strong performance, initiative, and leadership skills in previous work environments. APICS or NAPM certification a plus."



    Right here at the bottom, "must demonstrate indexing and v-look ups."



    Nobody gets THAT specific especially for something that is sooo easy to do.



  • Reply 10 of 15
    omegaomega Posts: 427member
    Are you a "seasoned material professional"?



    Also can you put a space somewhere in your forumla of the previous post so this page is not all screwy.
  • Reply 11 of 15
    macchinemacchine Posts: 295member
    Quote:

    Originally posted by Omega

    Are you a "seasoned material professional"?



    Also can you put a space somewhere in your forumla of the previous post so this page is not all screwy.






    I am a seasoned professional.



    The definition of the term material seems to be indeterminate, I am not affiliated with a larger consulting group or bonded, I can supply my own hardware and software.
  • Reply 12 of 15
    cosmonutcosmonut Posts: 4,872member
    Quote:

    Originally posted by tonton

    Less than 50=F, 50-59.9=D, 60-69.9=C, 70-79.9=B, 80 or above =A.



    That's generous. What grade level are these kids?
  • Reply 13 of 15
    rokrok Posts: 3,519member
    Quote:

    Originally posted by CosmoNut

    That's generous. What grade level are these kids?



    that's what i was gonna say, too. 80 and above = A? damn, would have saved me a lot of work in high school (where they broke the GPA and grades into 90-92 was A-, 93-96 was A, and 97 and above was A+).
Sign In or Register to comment.