Based on the pricing bands, I would like to khow how to input an amount of sales in the highlighted cell (B6) and automatically calculate an amount to charge in the cell below (B7)?

Hope you can help.
Many thanks

PS Don't seem to be able to upload excel files due to small flle size so hope this notepad file will suffice?

------------------------------------------------------------------------

Dear Colgor, Good Evening

The best way to do this is creating a little table for search.
It´s simple.

Suppose:

___D__________E
4____0________40
5___51________30
6__101________20
7__151________15

B6 = Nº of units = 250
B7 = Total charged -- Formula

Formula -- =B6*VLOOKUP(B6,D4:E7,2,TRUE)


Tell me if it worked for you.

Hi Mazzaropi and many thanks for your reply. Tried but it didn't work, but it is late and I'll definitely try again tomorrow. Thanks again.

I've had another go.

Not used VLOOKUP before, so thanks, as this function seems to be the right way to go, but the answer is still wrong.

The total should be £6,000 not £3750.

The reason being, the formula is calculating all all the sales at £15, as the number of sales is above 150, but it is not being calculated that way.

Instead, only the sales above 150 are being calculated at £15 each. The first 150 sales are being calculated broken down into the three lower pricing bands.

I will try and work out this VLOOKUP works and try to readjust the formula. If anyone can reply with the correct formula in the meantime before I post my progress that woulld be brilliant?

Thanks again
Colin

--------------------------------------------------------------------------

Dear Colin, Good Afternoon.

I developed a new formula to you and now, I believe that this formula is according your needs

I did a model to test this new formula.
It´s here : http://www.4shared.com/document/ZFIH...r_Pricing.html

There is a UNIQUE formula at E14 cell.

Please, test it and tell if it worked for you now.
Feel free to ask anything about it.

Hi Mazzaropi

The formula works perfectly, thank you! If you give me no more advice then I would still be very pleased. However, there are one or two aspects that you may be able to still give further advice for a perfect solution! - if you can spare the time?

• I would like to future-proof the spreadsheet, so that it works in several scenarios. For this to happen, I need to be able to edit the quantity and volume in the table. It would appear I can change the price but not the quantity? Is this doable?
• Is it possible to remove the formatting? I wish to cut and paste into a spreadsheet with its own format, but at present, I am finding it impossible to remove the colourful format that you have used. Very nice indeed! :-) Simply wrong for the spreadsheet I need to use the 
NB. I attempted to alleviate the problem by recreating the table and formula, but the formula using functions was difficult with my experience, so intend to find some tutorials to properly understand the IF, VLOOKUP and MATCH for future use.
• Can you tell me what 'Aux’ stands for?

Aprecio muito a vossa ajuda será este. Seu Inglês é muito melhor que o meu Português, mas o Google Translate função veio em meu socorro! Estou ansioso por ouvir de você novamente.

Muito melhores cumprimentos,
Colin

-------------------------------------------------------------------------
Dear Colin, Good Evening.

At first, I can say that for all your questions the answer is the same: YES, it´s totally possible to change all the parameters.
BUT, to do this, I need initially to prepare the worksheet to be TOTALLY INDEXED. This will cost me a little more of time.

As you can see, there is a 50 NUMBER, not a variable, in the formula.
This is the KEY of the process.

As the search formula was developed based at yours preceeding informations, I need to prepare the worksheet to be total flexible, and, as you said, to be future-proof and to attend your new necessities.

I´ll do this, probably, on this weekend, on my spare time.
On my next message I´ll answer all your questions with details.

Be sure that I´ll help you with this question.

Thanks, you are very kind. I look forward to hearing from you again when you can spare a moment to look into this a little further, as you describe.

Thanks again
Colin

-------------------------------------------------------------------------

Dear Colin, Good Afternoon.
Now, I have a solution that can be as flexible as you desire.
It´s here :
http://www.4shared.com/document/6ARa...r_Pricing.html

Your questions:
1) "...I would like to future-proof the spreadsheet, so that it works in several scenarios.
For this to happen, I need to be able to edit the quantity and volume in the table.
It would appear I can change the price but not the quantity?
Is this doable?..."
Answer:
Change Quantity and Price is totally possible, BUT, the model and the formula was build considering 4 bands of price.
Then, if you need more than this you must adjust the formula.

2) Is it possible to remove the formatting?
I wish to cut and paste into a spreadsheet with its own format, but at present,
I am finding it impossible to remove the colourful format that you have used. Very nice indeed! :-)
Simply wrong for the spreadsheet I need to use the 
Answer:
At this new model you have just a plain table.

3) NB. I attempted to alleviate the problem by recreating the table and formula, but the formula using functions was difficult with my experience, so intend to find some tutorials to properly understand the IF, VLOOKUP and MATCH for future use.
Answer:
The IF FUNCTION is a BASIC function and you must know it by heart. It´s fundamental if you want to develop things using EXCEL. The other two are a little more advanced, but nothing so sofisticated.
To know about available functions, type at any cell the signal of EQUAL (=) and at tool bar click on the Fx symbol.
It will open a dialog box and show all the functions that your Excel have.
Choose one and at the “Help about this function” you´ll find a very useful tutorial about this specific function.

4) Can you tell me what 'Aux’ stands for?
Answer:
This column is a auxiliar to the pricing table.
It acumulates the total about the preceeding band to faster the calculus.
Please, don´t change the formulas there.
The places that you can alter at this table are Qty and Price


I verified as much as possible the precision of the formulas to avoid error at my job to you, but if you find one, please let me know.

Attention: As Excel is a cell reference software, be smart when changing table and input field of place.


Feel free to ask anything about it.

Have a nice weekend.

Hi

This is much much appreciated, looks great. I am working on another job but in the next couple of days I will test out what you have done and feedback. Thanks again.

Colin

Hi Mazaropi

Finally got back to this job, then I just tried to open the file to try to test the formula but server at 4shared.com is down, will try again later. Thanks.

------------------------------------------------------------------------

Dear Steve, Good Morning.

I tried 4shared and now the site is on.
For your convenience I attached the worksheet here.
Fell free to ask anything about it.

The orginal spreadsheet uploaded this morning and the formula worked perfectly, future proof and flexible, thank you very much! :-)

I will take a look at the tutorials you mentioned. Again, many thanks!
Colin