+ Reply to Thread
Results 1 to 14 of 14

Whats wrong in this formula

  1. #1
    Registered User
    Join Date
    03-13-2017
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    10

    Whats wrong in this formula

    Hi

    Need help with the below formula. What went wrong with the below formula

    =IF(E21=0,0,IF(AND(E21>0,E21<50)5,IF(AND(E21>50,E21<=70),10,IF(AND(E21>=70),15))))

    Below is the range for E21 cell :
    -------------------------------
    up to 50% - 5 points ( i.e b/w 0 to 50 )
    50 to 70 % - 10 points
    70 % + - 15 points"

    Note: if the value is less then 0 then I need the cell updated with "0"
    Error : Formula parse error.

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Whats wrong in this formula

    Try this ...

    =IF(E21<=0,0,IF(E21<50,5,IF(E21<=70,10,15)))

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Whats wrong in this formula

    There should be a comma before the 5

    Do it this way, it's easier, from the highest condition descending

    =IF(E21>=70,15,IF(E21>50,10,IF(E21>0,5,0)))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Whats wrong in this formula

    another way:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Whats wrong in this formula

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Registered User
    Join Date
    03-13-2017
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    10

    Re: Whats wrong in this formula

    Great... so simple :-)
    Thanks for your help

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Whats wrong in this formula

    If the basic problem is solved
    - it's always a good practice to show respect to the person(s) who have helped you
    - is a click on the Add Reputation first (left lower corner of the post of person(s) who helped you) and then
    - mark the thread as SOLVED (top right corner over your first post - Thread Tools). This is important for all of us,

    Thanks

  8. #8
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Whats wrong in this formula

    Quote Originally Posted by AlKey View Post
    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Oops!
    E21 with value of 0 will return 5.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Whats wrong in this formula


    =LOOKUP(E21,{0,51,70},{5,10,15})
    it should be chaged to IF(E21<=0,0,LOOKUP(E21,{0,0.51,0.7},{5,10,15}) (not tested) because in E21 are % values

  10. #10
    Registered User
    Join Date
    03-13-2017
    Location
    Bangalore
    MS-Off Ver
    2007
    Posts
    10

    Re: Whats wrong in this formula

    When I try this way 0 also taking 5 point. If the cell is 0 then the result need to be "0"
    =LOOKUP(E21,{0,51,70},{5,10,15})

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Whats wrong in this formula

    Then this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Whats wrong in this formula

    Just because the actual question was never answered...
    What went wrong with the below formula

    =IF(E21=0,0,IF(AND(E21>0,E21<50)5,IF(AND(E21>50,E21<=70),10,IF(AND(E21>=70),15))))

    Error : Formula parse error.
    You're missing a COMMA after <50)
    It Should have been
    =IF(E21=0,0,IF(AND(E21>0,E21<50),5,IF(AND(E21>50,E21<=70),10,IF(AND(E21>=70),15))))
    Last edited by Jonmo1; 03-13-2017 at 09:27 AM.

  13. #13
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Whats wrong in this formula

    Quote Originally Posted by Jonmo1 View Post
    Just because the actual question was never answered...
    I answered it, Post #3.

    Bah! No one ever reads what I type :Frown:
    Only when I get it wrong

  14. #14
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Whats wrong in this formula

    Bah! No one ever reads what I type
    Only when I get it wrong
    Welcome to an internet forum. LOL.

    Sorry, I was looking for and didn't find a 'corrected version' of the original formula.
    My bad.

+ 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. [SOLVED] Weekday formula whats wrong
    By rogrand in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-05-2014, 09:25 PM
  2. Can someone tell me whats wrong with this formula???
    By bopsgtir in forum Excel General
    Replies: 8
    Last Post: 07-29-2011, 01:23 PM
  3. Whats wrong with this formula?
    By blue-bullet in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-20-2009, 06:51 AM
  4. whats wrong with this formula?
    By only_lonely in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-14-2008, 08:23 AM
  5. Whats wrong with this formula?
    By Shane Moore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-06-2006, 02:57 AM
  6. Whats Wrong With This Formula
    By curly88 in forum Excel General
    Replies: 3
    Last Post: 10-01-2006, 10:02 PM
  7. Whats wrong with my formula?
    By DKerr in forum Excel General
    Replies: 2
    Last Post: 09-05-2006, 07:02 AM
  8. whats wrong with the formula?
    By Gary in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-03-2006, 06:40 AM

Tags for this Thread

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