Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Custom formatting text (Excel 2003)

    Hi All,

    I recently encountered something very odd dealing with custom formatting.

    I have a spreadsheet with student averages. Based on average, there is a formula with a lookup table to convert average to letter grade. For example, below 70=F, 70-76.99=C, 77-79.99=C+, etc. Of course, done in "proper" VLOOKUP layout.

    If the student's average is close to the next letter grade, I may give him/her the benefit of the doubt and increase their letter grade to the next level. For example, if the average is 76.5, I'll probably give the student a C+. I had a post a few weeks ago asking how to show this in the proper cells but NOT increase the letter grade. See <post#=570930>post 570930</post#> if interested.

    I've not implemented the suggestions there but I don't think that matters in this instance.

    What I need to do now is hand my final grade sheet. So if a student had a 76.5, the lookup returns a C say in cell H20. If I just printed that off to hand in, the student's grade would be C but I want it to be C+. Of course, I could wipe out the formula in H20 and just put a C+. I don't want to do that.

    So what I decided to do is use a custom format for the text part of cell H20. Of course, H20 would always be text based on the lookup. So I thought using a custom format of ;;;C+ would be just fine. Before you all jump on me and say it should have been ;;;"C+", let me say 2 things in my defense:
    - it worked as ;;;C+
    - I forgot the quotes when I first did this

    I had a few other grades to change to different letters. Next came a student with an 88.9 which is a B+. I wanted to give her an A (90 or higher). So I typed ;;;A and no problem. However, when looking at the custom format list, Excel had changed this to ;;;A. Whatever it takes to make Excel happy is OK with me. Note that Excel had not changed ;;;C+ to ;;;C+

    Lastly, I had an 86.5, which is a B, to change to a B+ (87-89.99). Sensing no problems with the above, I typed ;;;B+ Now Excel was unhappy. It gave me an error message saying "Microsoft Office Excel cannot use the number format you typed." Now I had to type this as ;;;"B+"

    In summary:


    <table border=1><td>Average</td><td>VLOOKUP Grade</td><td>Desired Grade</td><td>I typed</td><td>Excel's Action</td><td>76.5</td><td>C</td><td>C+</td><td>;;;C+</td><td>accepted as typed</td><td>88.9</td><td>B+</td><td>A</td><td>;;;A</td><td>changed to ;;;A</td><td>86.5</td><td>B</td><td>B+</td><td>;;;B+</td><td>Excel error msg</td></table>


    Any clues as to this behavior? Why did Excel accept one, change one, and complain about one? Is there something magical in terms of some control actions about the letters used in a text custom format? I didn't see anything in the Help that explained this.

    TIA.

    Fred

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom formatting text (Excel 2003)

    I wouldn't spend much time worrying about it. The "official" ways to include a literal character in a format are 1) put quotes around the character and 2) put a backslash before the character (so B+ would have worked too). Apparently, the format parser allows you to use some characters without quotes or backslash. So what?

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Custom formatting text (Excel 2003)

    Thanks for the info, Hans. I didn't know that I could use a back-slash before this exercise. I thought the A was some kind of escape that Excel was using (at least some time). Based on what I saw on the first 2 items, I thought Excel was inserting the backslash for a 1-character format not in quotes. But I just tried typing another custom format as ;;;hello and Excel gave the error msg that I got earlier.

    Anyway, I'll stick with using quotes.

    Fred

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom formatting text (Excel 2003)

    The backslash only affects a single following character, so your example would have to be ;;;hello, but of course ;;;"hello" is clearer and more efficient.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •