**Question description:**

*This user has given permission to use the problem statement for this blog.*

Please help me figure out what is wrong with this formula:
=IF(AND(C3>=1,C3<=50,200,IF(AND(C3>=51,C3<=100),400,IF(AND(C3>=101,C3<=150),600,IF(AND(C3>=151,C3<=200),800,IF(AND(C3>=201,C3<301),1000,IF(AND(C3>=301,C3<401),1200,IF(AND(C3>=401,C3<501),1400,IF(C3>=501,1600,""))))))))

Solved by B. L. in 40 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
04/04/2018 - 06:03

Welcome to Excel Pro

Excelchat Expert
04/04/2018 - 06:04

Do you want us to assist you in finding where the error is in the IF function?

User
04/04/2018 - 06:05

Yes please. I originally got this formula from you. It was working however the original version allowed a value for the user number (C2) of 0. So I needed to change the first part from an IF to an IF And to have it check to see that the value is between 1 and 50 not 0 and 50. In making that change I broke something ...

Excelchat Expert
04/04/2018 - 06:07

Okay. Let me go through it for a moment please.

Excelchat Expert
04/04/2018 - 06:09

Thank you for patience.

Excelchat Expert
04/04/2018 - 06:10

For any function, all its parameters must be enclosed in parenthesis.

Excelchat Expert
04/04/2018 - 06:10

Let me illustrate this in the sheet to the right.

Excelchat Expert
04/04/2018 - 06:11

And has always has two parameters.

Excelchat Expert
04/04/2018 - 06:11

For the case our case:

Excelchat Expert
04/04/2018 - 06:11

=AND(C3>=1,C3<=50)

User
04/04/2018 - 06:12

I'm not certain if I made my question clear. I originally received this formula and it worked fine:

User
04/04/2018 - 06:12

=H35=IF(A2<=50,200,IF(AND(A2>=51,A2<=100),400,IF(AND(A2>=101,A2<=150),600,IF(AND(A2>=151,A2<=200),800,IF(AND(A2>=201,A2<301),1000,IF(AND(A2>=301,A2<401),1200,IF(A2>401,1400,"")))))))

User
04/04/2018 - 06:13

However if you look closely you will see that it allows a valued for A2 to be zero - the very first parameter - I need that to be changed to and If And statement to make the range from 1-50 not less than 50

User
04/04/2018 - 06:14

Can you please correct the formula I was given to make sure that a 0 value in A2 does not return an answer of $200. That should apply at 1-50 not 0-50.

Excelchat Expert
04/04/2018 - 06:16

I see the first parameter is: A2<=50

User
04/04/2018 - 06:16

Right - but I need it to be changed to apply only for 1-50 not 0-50 ...

User
04/04/2018 - 06:17

so greater than or equal to 1 and less than 51

Excelchat Expert
04/04/2018 - 06:17

Thank you. I get you now.

Excelchat Expert
04/04/2018 - 06:17

We will then replace the condition with the AND function as on the right handside.

User
04/04/2018 - 06:18

Can you please make the change. I understand what needs to be done but when I did it something was missing and I can't get it to correct and am on a deadline thank you so much

Excelchat Expert
04/04/2018 - 06:18

Ensure you put the parenthesis in the function.

Excelchat Expert
04/04/2018 - 06:19

Like this:

Excelchat Expert
04/04/2018 - 06:19

=IF(AND(C3>=1,C3<=50),200,IF(AND(C3>=51,C3<=100),400,IF(AND(C3>=101,C3<=150),600,IF(AND(C3>=151,C3<=200),800,IF(AND(C3>=201,C3<301),1000,IF(AND(C3>=301,C3<401),1200,IF(AND(C3>=401,C3<501),1400,IF(C3>=501,1600,""))))))))

Excelchat Expert
04/04/2018 - 06:19

Notice the closing bracket after the <=50

Excelchat Expert
04/04/2018 - 06:20

Now it should work work without any error.

Excelchat Expert
04/04/2018 - 06:21

Does that solve the problem?

User
04/04/2018 - 06:21

I believe it did, thank you.

Excelchat Expert
04/04/2018 - 06:21

Welcome again!

Excelchat Expert
04/04/2018 - 06:22

All the best. Enjoy the rest of the day!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*