# Whats wrong in this formula

1. ## 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.  Register To Reply

2. ## Re: Whats wrong in this formula

Try this ...

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

3. ## 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)))  Register To Reply

4. ## Re: Whats wrong in this formula

another way:

Formula:  `Please Login or Register  to view this content.`  Register To Reply

5. ## Re: Whats wrong in this formula

Or this
Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## Re: Whats wrong in this formula

Great... so simple :-)  Register To Reply

7. ## 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   Register To Reply

8. ## Re: Whats wrong in this formula Originally Posted by AlKey Or this
Formula:  `Please Login or Register  to view this content.`
Oops!
E21 with value of 0 will return 5.  Register To Reply

9. ## 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  Register To Reply

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})  Register To Reply

11. ## Re: Whats wrong in this formula

Then this
Formula:  `Please Login or Register  to view this content.`  Register To Reply

12. ## 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))))  Register To Reply

13. ## Re: Whats wrong in this formula Originally Posted by Jonmo1 Just because the actual question was never answered...

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

14. ## 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.  Register To Reply

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