+ Reply to Thread
Results 1 to 13 of 13

Case Function, Greater than, Less than etc

  1. #1
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Case Function, Greater than, Less than etc

    Is it possible to use the case function to have greater than, equal to etc

    I can do this using an IF statement, but trying to broaden my knowledge to see how the case function works.

    I've searched for the correct usage and examples, but can't find anything.

    What I'm trying to achieve is below, if this is possible, what is the correct context to use?

    Please Login or Register  to view this content.
    Last edited by Steveingmc; 02-12-2015 at 11:10 AM.

  2. #2
    Forum Contributor
    Join Date
    09-05-2012
    Location
    Dubai
    MS-Off Ver
    Office 365
    Posts
    409

    Re: Case Function, Greater than, Less than etc

    Its Almost correct, Just small syntax need to change and you need to define your variables and initialize your comparing variable... and declare your variable result
    Please Login or Register  to view this content.
    Last edited by naveenmarapaka; 02-12-2015 at 10:17 AM.

  3. #3
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Case Function, Greater than, Less than etc

    Thanks for the reply. My initial problem is still there. The syntax

    Case Is >=83 and <85

    Is highlighting red, with an error "Compile error: Expected Expression"

    This is part I cant find an example of when searching. I'm thinking the 'and' part is incorrect?

  4. #4
    Forum Contributor
    Join Date
    09-05-2012
    Location
    Dubai
    MS-Off Ver
    Office 365
    Posts
    409

    Re: Case Function, Greater than, Less than etc

    Modified....
    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Case Function, Greater than, Less than etc

    This should do the job :

    Please Login or Register  to view this content.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *

  6. #6
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Case Function, Greater than, Less than etc

    edit: Just seen Xlbiznes reply, will try that

    Response below was in response to naveenmarapaka

    Thanks again. I've already tried that. The problem is, if the result is 86.000, what should it return, 80 or 100

    Hence my use of >= and <

  7. #7
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Case Function, Greater than, Less than etc

    It may help if i provide a little background. The result in Cell A1 could be 83.5, 83.9 etc, which scores 40 points, the result of 84.5, scores 80 points and so on.

    Using Xlbiznes example a result of 85.5 scores 'Case else' 125 points, where it should score 80.

  8. #8
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Case Function, Greater than, Less than etc

    @Steveingmc,

    firstly you are dealing not with integers but with decimal values hence you need to use the double data type.
    i guess this code will solve your issues.

    Please Login or Register  to view this content.
    please change the from, to values to suit your needs.

  9. #9
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Case Function, Greater than, Less than etc

    Thanks. Unfortunately, same problem. If the result is 85.99, the result is Case Else = 125

    This is my reasoning for using <86 As everything below 86.0 would be captured.

  10. #10
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Case Function, Greater than, Less than etc

    Please add another 9 to your decimal values in the case function.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Case Function, Greater than, Less than etc

    Thanks I understand and have tried that before. I've now left it at 85.999999999999 to capture everything.

    Still think it would be easier if there was a simple less than option.

  12. #12
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Case Function, Greater than, Less than etc

    You can also try the if conditions.

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    08-12-2012
    Location
    Cambs
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Case Function, Greater than, Less than etc

    That's how I originally did. I just thought I'd get to grips with the CASE function as I'd never used it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Function and CASE
    By malcmail in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-21-2014, 09:19 AM
  2. Need help to change multiple criteria from if function to Case function.
    By indkitty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2012, 10:42 PM
  3. Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  4. Case function
    By Rage in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2011, 08:32 AM
  5. Select Case, Less and Greater than?
    By Sami82 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-13-2005, 01:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1