+ 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,357

    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,038

    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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Whats wrong in this formula

    another way:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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,038

    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
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    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,038

    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