# 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.

2. ## Re: Whats wrong in this formula

Try this ...

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

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)))

4. ## Re: Whats wrong in this formula

another way:

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

5. ## Re: Whats wrong in this formula

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

6. ## Re: Whats wrong in this formula

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

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

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.

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

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. ## Re: Whats wrong in this formula

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

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))))

13. ## Re: Whats wrong in this formula

Originally Posted by Jonmo1
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. ## 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.

#### Thread Information

##### Users Browsing this Thread

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

#### 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