# Stupid Excel Tricks

Posted:
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?

Posts: 8,760member
Pretty easy, a lookup into the ASCII character table based on the decile of the score.
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.
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)
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.
Posts: 615member
Pretty nice. This is a cool little app for those wanting a GPA calculator. Just an FYI.
Posts: 6,523member
Excel is kewl
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.
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 !!!

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.

=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 !!!"

=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.
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.

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.
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.
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?