Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Loungers - I came across a spreadsheet that uses the formal below to calculate the numbers under the Score heading (sorry not sure how to insert a table) - I'm interested in understanding the value of using the CODE function in this situation as it seems to be overly complex for something that I believe is a fairly simple formula -

    Weight Achieved Score
    5 0 0
    5 4 20
    5 4 20
    5 4 20
    5 4 20
    5 4 20
    5 * N/A
    120 100



    =SUM(C47:C53)*4-IF(CODE(D47)=42,(C47*4),0)-IF(CODE(D48)=42,(C48*4),0)-IF(CODE(D49)=42,(C49*4),0)-IF(CODE(D50)=42,(C50*4),0)-IF(CODE(D51)=42,(C51*4),0)-IF(CODE(D52)=42,(C52*4),0)-IF(CODE(D53)=42,(C53*4),0)[list]

    Any thoughts?

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='verada' post='785871' date='23-Jul-2009 08:44']Loungers - I came across a spreadsheet that uses the formal below to calculate the numbers under the Score heading (sorry not sure how to insert a table) - I'm interested in understanding the value of using the CODE function in this situation as it seems to be overly complex for something that I believe is a fairly simple formula -

    Weight Achieved Score
    5 0 0
    5 4 20
    5 4 20
    5 4 20
    5 4 20
    5 4 20
    5 * N/A
    120 100



    =SUM(C47:C53)*4-IF(CODE(D47)=42,(C47*4),0)-IF(CODE(D48)=42,(C48*4),0)-IF(CODE(D49)=42,(C49*4),0)-IF(CODE(D50)=42,(C50*4),0)-IF(CODE(D51)=42,(C51*4),0)-IF(CODE(D52)=42,(C52*4),0)-IF(CODE(D53)=42,(C53*4),0)[list]

    Any thoughts?[/quote]

    =CODE(text) returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by computer. ANSI is character set used by Windows. This can be viewed in formula help.

    [attachment=84814:untitled.JPG]
    Attached Images Attached Images
    Regards
    Prasad

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Further to Prasad's answer, 42 is the ASCII/ANSI code of the asterisk character *, so the condition

    CODE(D47)=42

    is equivalent to

    the value of D47 starts with an *

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Prasad & Hans

    Thanks for the reply

    How do I find out what the ASCII/ANSI code are and any thoughts on a simpler alternative?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See ANSI character set and equivalent Unicode and HTML characters.

    If column D only contains a number or an asterisk *, the formula can be simplified to

    =SUMIF(D4753,"<>~*",C47:C53)*4

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Hans - that looks much easier.

    As a matter of interest, what does ~ do?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The asterisk * acts as a wildcard in many situations in Excel, meaning "any number of characters". The ~ before it tells Excel to treat * as a literal character.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks for all your help - AGAIN!

Posting Permissions

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