Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc