+ Reply to Thread
Results 1 to 5 of 5

If Equal to or less than formula

  1. #1
    Daywalker
    Guest

    If Equal to or less than formula

    Hi,

    Have a problem trying to get a formula to work.

    I am trying to set a scorecard up where it will give points depending on the % actual.

    For example:

    if less than 60%, then 0,
    if equal to or greater than 60% but less than 75%, then 1
    if equal to greater than 75% but less than 80%, then 2 and so on.

    I have been playing around with the example below, but can't seem to get it working.

    [php]=IF(A1<60%,0,IF(A1>A1<75%,1,))[php]

    Maybe it 's because I pickled my brains last night.

    Cheers,

    Daywalker
    Last edited by Daywalker; 10-31-2009 at 07:07 AM.

  2. #2
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: If Equal to or less than formula

    Almost right..

    =IF(A1<60%,0,IF(AND(A1>60%,A1<75%,1,))

    EDIT

    Sorry, a typo,

    =IF(A1<60%,0,IF(AND(A1>60%,A1<75%),1))
    Last edited by jj72uk; 10-31-2009 at 06:59 AM.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: If Equal to or less than formula

    jj72uk, you don;t need to repeat the 60% test given it's already accounted for in the first test (an IF will exit upon first true -- and in truth it should read >=60%), ie:

    =IF(A1<60%,0,IF(A1<75%,1,IF(A1<80%,2,3)))

    however this type of thing is far better in a LOOKUP approach

    =LOOKUP(A1,{0,0.6,0.75,0.8},{0,1,2,3})

    on a final note... if your output is a simple increment like 0,1,2 etc then you can use a basic MATCH

    =MATCH(A1,{0,0.6,0.75,0.8})-1

    the -1 to account for base of 0

    You may need to validate A1 prior to testing - this is not clear.

  4. #4
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: If Equal to or less than formula

    Sorry for the mis-guidance Daywalker!

    Just shows we can all still learn!

  5. #5
    Daywalker
    Guest

    Re: If Equal to or less than formula

    Thanks for your help gents.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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