Fun with functions in Excel/Open Office?

Factual Questions
1

Well, the 7th and 8th graders at my school are taking their final exams this week (specifically, Thursday through Saturday) and this year there’s a twist in the routine. The students must take a speaking test administered by yours truly. Of course, I went technological and typed up the scores in an OpenOffice spreadsheet. Everyone was happy until the grading standard changed. Luckily, I don’t have to retest any of the students. I just have to change the scores to the number at the bottom of each tier.

Here are the tiers:

1st Tier: 25
2nd Tier: 22
3rd Tier: 19
4th Tier: 16
5th Tier: 13
6th Tier: 10
7th Tier: 0

So, if someone had a score of 23, then the actual score should be 22; a score of 14 would be 13; and so on. What’s more is that there are four categories for each test and a grade for each category.

I’ve almost never used functions in spreadsheets other than simple calculations. I’d love to have the spreadscheet in both OpenOffice and Excel use just one function to generate the proper score instead of going through and changing over 2,600 cells individually.

What should the function be?

More fun: I have to get this all done by midnight Thursday Korea Standard Time.

Thanks!

2

Check out the LOOKUP function.

3

I’m still confused by the syntax in the function sample.

4

If I understood correctly what you are trying to do, here is something that should work:


=LOOKUP(A14,{0,10,13,16,19,22,25},{0,10,13,16,19,22,25})

where the first argument references the original score you have. The second two arguments specify the values that you want to compare the first argument to. Excel will find the largest value that is less than or equal to the first argument. So, for example, if you enter


=LOOKUP(23,{0,10,13,16,19,22,25},{0,10,13,16,19,22,25})

Excel will return “22”.

Please let me know if this works for you.

5

Set up a simple table with 0, 10, 13, 16, 19, 22, 25 in two side-by-side columns. So it’s a 7x2 array with the numbers in each column in ascending order. Suppose this table’s name is Tiers. To convert a score of X just use the function VLOOKUP(X, Tiers, 2, 1).