# Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula that shows in box a different price depending on what value is inputted into another cell. I need one formula that shows: if 0 is put in the 0 is the answer. If a value equal or less than 25 is entered, then 13.32 will show in the cell the formula is being entered. If a value equal or more than 26 and equal and less than 50, then 17.32 will show in the cell the formula is being entered. If a value equal or more than 51 and equal and less than 100, then 34.13 will show in the cell the formula is being entered. If a value equal or more than 101 and equal and less than 250, then 42.64 will show in the cell the formula is being entered. If a value equal or more than 251 and equal and less than 500, then 50.64 will show in the cell the formula is being entered. If a value equal or more than 501 and equal and less than 1000, then 53.32 will show in the cell the formula is being entered. If a value equal or more than 1001 and equal and less than 1500, then 75.97 will show in the cell the formula is being entered. If a value equal or more than 1501 and equal and less than 2000, then 89.31 will show in the cell the formula is being entered. If a value equal or more than 2001 and equal and less than 2500, then 97.31 will show in the cell the formula is being entered. If a value equal or more than 2501 and equal and less than 5000, then 175.97 will show in the cell the formula is being entered. Then for 10000 or over is 339.87 that will show in the cell the formula is being entered. The value in which will be used to determinate cost is taken out of B12. If there is time, then I would like another formula. A formula that shows 1 and half percent of c37 plus 15. Then times by 1.2 for VAT. As well as, if A2+A5 adds up to 400 and above, then it is to add 10 pence (.10) per every digit above 400, then times 1.2 for VAT.
Solved by I. Y. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/10/2017 - 05:37
User 19/10/2017 - 05:38
thanks. do u understand the description?
Excelchat Expert 19/10/2017 - 05:38
Yes. i am writing the ranges given.
User 19/10/2017 - 05:38
I cant upload the file due to an NDA agreement
User 19/10/2017 - 05:38
thank u
Excelchat Expert 19/10/2017 - 05:38
It takes time
Excelchat Expert 19/10/2017 - 05:38
NDA?
Excelchat Expert 19/10/2017 - 05:38
User 19/10/2017 - 05:38
means I cant share the file by law
Excelchat Expert 19/10/2017 - 05:42
Hi, I am attempting the first question. The descriptions says, multiple questions. As per our policies, we are happy to provide one solution at a time
User 19/10/2017 - 05:42
thats fines
User 19/10/2017 - 05:42
fine
Excelchat Expert 19/10/2017 - 05:42
Ok. Thank you. I need to test all the ranges. So, it takes time.
Excelchat Expert 19/10/2017 - 05:42
User 19/10/2017 - 05:43
k
Excelchat Expert 19/10/2017 - 05:50
What about between 5001 to 10000?
User 19/10/2017 - 05:50
that will show as 339.87
Excelchat Expert 19/10/2017 - 05:50
That is greater than 10000
Excelchat Expert 19/10/2017 - 05:51
May be a mistake. I will just use the same number
Excelchat Expert 19/10/2017 - 05:51
for >=5001
User 19/10/2017 - 05:51
If I purchase anything higher than 25, it will charge me for 50. and so forth
User 19/10/2017 - 05:52
if I buy more than 50, it will charge me for 100
Excelchat Expert 19/10/2017 - 05:52
Ok
Excelchat Expert 19/10/2017 - 05:52
The range after 5000 is not clear
Excelchat Expert 19/10/2017 - 05:52
So, i m using >=5001, 339.87
User 19/10/2017 - 05:52
If I buy more than 5000, then I will be charged for 10000
Excelchat Expert 19/10/2017 - 05:53
Ok. But, the range given as >10000 only. So, only i have asked
User 19/10/2017 - 05:53
0-25 is 13.32
Excelchat Expert 19/10/2017 - 05:53
If a value equal or more than 2501 and equal and less than 5000, then 175.97 will show in the cell the formula is being entered. Then for 10000 or over is 339.87 that will show in the cell the formula is being entered.
Excelchat Expert 19/10/2017 - 05:53
Check here: 2501 - 5000: 175.97
Excelchat Expert 19/10/2017 - 05:54
User 19/10/2017 - 05:54
hang on. let me check priced
Excelchat Expert 19/10/2017 - 05:54
So, the range between 5001-10000 is missing
Excelchat Expert 19/10/2017 - 05:56
=IF(A2=0,0,VLOOKUP(A2,\$C\$3:\$E\$13,3,TRUE))
Excelchat Expert 19/10/2017 - 05:56
Here is the formula for B2 cell when the input is in A2 cell
User 19/10/2017 - 05:58
0-25 is 13.32 - 26-50 is 17.32 - 51-100 is 34.13 - 101-250 is 42.64 - 251-500 is 50.64 - 501-1000 is 53.32 - 1001-1500 is 75.97 - 1501-2000 is 89.31 - 2001-2500 is 97.31 - 2501-5000 is 175.97 - 5001-10000 is 339.87
Excelchat Expert 19/10/2017 - 05:58
ok
Excelchat Expert 19/10/2017 - 05:58
It is done.
User 19/10/2017 - 05:58
i dont understand the formula. I may need to change it
Excelchat Expert 19/10/2017 - 05:58
=IF(A2=0,0,VLOOKUP(A2,\$C\$3:\$E\$13,3,TRUE))
User 19/10/2017 - 05:59
wait
Excelchat Expert 19/10/2017 - 05:59
Please check the ranges given in C, D, E columns
Excelchat Expert 19/10/2017 - 05:59
Based on that, i have used vlookup to get the result as required
User 19/10/2017 - 05:59
the numbers you have entered are not in the spreadsheet. You will need to include those numbers in the formula
User 19/10/2017 - 05:59
i do not want vlookup
User 19/10/2017 - 05:59
i need all the numbers in the formula
Excelchat Expert 19/10/2017 - 05:59
Ok. With formula
Excelchat Expert 19/10/2017 - 06:00
Ok. I will update. I thought, it is simpler approach
User 19/10/2017 - 06:02
It should be a long formula once you are done.
User 19/10/2017 - 06:03
please remember, the numbers are not on spreadsheet. Those numbers will need to be in the forumla. This also helps me edit if need be.
Excelchat Expert 19/10/2017 - 06:03
Sure. i am working on that
User 19/10/2017 - 06:04
so if b12 was inputted a value, the other cell will show the numbers
Excelchat Expert 19/10/2017 - 06:05
=if(B12=0,0,if(B12<=25,13.32,if(B12<=50,17.32,if(B12<=100,34.13,if(B12<=250,42.64,if(B12<=500,50.64,if(B12<=1000,53.32,if(B12<=1500,75.97,if(B12<=2000,89.31,if(B12<=2500,97.31,if(B12<=5000,175.97,339.87)))))))))))
Excelchat Expert 19/10/2017 - 06:05
Here is the formula
Excelchat Expert 19/10/2017 - 06:05
B12 is input cell
User 19/10/2017 - 06:06
ok, let me test it out
Excelchat Expert 19/10/2017 - 06:06
Sure
User 19/10/2017 - 06:06
i get err:508
Excelchat Expert 19/10/2017 - 06:06
Excelchat Expert 19/10/2017 - 06:07
Is it B12
Excelchat Expert 19/10/2017 - 06:07
and where you have kept the formula?
User 19/10/2017 - 06:07
i am entering in b12. the formula is in e12
Excelchat Expert 19/10/2017 - 06:07
Please check in the preview sheet and try changing it
Excelchat Expert 19/10/2017 - 06:07
Ok. I will change it to e12 then
Excelchat Expert 19/10/2017 - 06:08
Ok. The formula remains same
User 19/10/2017 - 06:08
no, the value is in b12 and should show the cost in E12
Excelchat Expert 19/10/2017 - 06:08
Yes that is right.
Excelchat Expert 19/10/2017 - 06:08
B12 is input
Excelchat Expert 19/10/2017 - 06:08
and output will be in E12
User 19/10/2017 - 06:08
then why is it not working for me
User 19/10/2017 - 06:09
i copied and pasted what you wrote
Excelchat Expert 19/10/2017 - 06:09
Please copy the whole formula including brackets
User 19/10/2017 - 06:09
i have
Excelchat Expert 19/10/2017 - 06:09
Excelchat Expert 19/10/2017 - 06:09
Ok
Excelchat Expert 19/10/2017 - 06:09
Error 508?
User 19/10/2017 - 06:09
yes
Excelchat Expert 19/10/2017 - 06:09
Are you running any macro?
User 19/10/2017 - 06:09
openoffice
Excelchat Expert 19/10/2017 - 06:09
Normally, these type of errors will not come formulas
Excelchat Expert 19/10/2017 - 06:10
Ok.
Excelchat Expert 19/10/2017 - 06:10
Excelchat Expert 19/10/2017 - 06:10
May be the format is different
User 19/10/2017 - 06:10
I can not sue to NDA remember
User 19/10/2017 - 06:10
due
User 19/10/2017 - 06:10
I have a signed agreement not to share file
Excelchat Expert 19/10/2017 - 06:11
Excelchat Expert 19/10/2017 - 06:11
I will send you a file
Excelchat Expert 19/10/2017 - 06:12
https://www.dropbox.com/s/t786quo7uczsu42/Untitled.ods?dl=0
Excelchat Expert 19/10/2017 - 06:12
User 19/10/2017 - 06:12
dont have dropbox
Excelchat Expert 19/10/2017 - 06:13
It is not required
User 19/10/2017 - 06:13
mate, it should be as simple as copy and pasting your formula
Excelchat Expert 19/10/2017 - 06:13
Excelchat Expert 19/10/2017 - 06:13
User 19/10/2017 - 06:13
no, does not let me. Like I said, dont have drop box
Excelchat Expert 19/10/2017 - 06:14
Ok. I will send you the file to your mail from our admin.
Excelchat Expert 19/10/2017 - 06:14
I have tested it. It is working fine.
User 19/10/2017 - 06:14
not for me
Excelchat Expert 19/10/2017 - 06:14
Not sure, what is the problem. So, i am sending you the file to mail. Please check in that.
User 19/10/2017 - 06:15
i fixed it. I had to replace the , with ;
Excelchat Expert 19/10/2017 - 06:15
Ok. The format is different in open office. Great to hear that
User 19/10/2017 - 06:16
Excelchat Expert 19/10/2017 - 06:16
You are welcome
Excelchat Expert 19/10/2017 - 06:16
Hope, it is working fine
Excelchat Expert 19/10/2017 - 06:16
User 19/10/2017 - 06:16
u 2
Excelchat Expert 19/10/2017 - 06:16
Please end the session and leave 5-stars

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.