**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.*