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.