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
Hi, It's pleasure to help you today:)
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
Is it your file?
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
I am downloading the file and working on it.
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
Then, a jump to >10000
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
What is your input cell?
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
Please paste in formula bar
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
Can you please download this file and open in open office
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
You can download file from here
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
Please download from this link
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
You can click on download anyway option.
Excelchat Expert
19/10/2017 - 06:13
Please goto File / Download as open office file
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
thank you for your help
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
Have a great day ahead:)
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.
