Need help building a complex(ish?) formula;

So basically - I have built a simple spreadsheet to display set prices at specific quantities for a supplier. I have added cells for the user to input different quantities between the set ones, for example, a price is set at 1000 in cell B20 and another for 1500 in B22. If I want to know what 1200 will cost I enter this into A21 using the formula set in B21 =(B22-B20)/(A22-A20)*(A21-A20)+B20.

Now this works perfectly fine but that means I have a custom quantity row in between each set quantity row due to the difference in prices not being universal. Obviously this looks rather messy and I'd like a much cleaner version.
From my knowledge should it not be possible to have a worksheet set up where I can just enter a quantity and it displays the price (using vlookup?).

My idea is a formula that works along these lines;
if cell value is great than (a) or less than(b) then ( subtract (acost) from (bcost) ) and divide by ( (b) minus (a)) then ( multiply cell value minus (a) ) plus (acost)

Sorry if this is badly explained, if I can make it any clearer please let me know!

Yes that should be doable, but it's quite difficult to visualize your setup and possible solution.

Can you attach a sample workbook?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

The example worksheet has three tabs;

The first "Desired Calculator" is how I want it to look,
The second "Set Prices" is the table I imagine would be used to draw data from using the vlookup function.
The third "Current Calculator" is the table I am using at the moment.

Just so you know what your looking at this is a table to show prices for specific printing processes over two sheet sizes.

Please Login or Register  to view this content.