Help Calculating tiered pricing with price breaks

[ToroMata]

Hi to all... I'm in need of help as I 'm a newbie to Excel as far as formulas and will appreciate any assistance, I'm using Excel 2010.
I'm trying to calculate a tiered pricing format with price breaks in Excel to calculate fee's to my clients for ordering their products.
I tried using different models like VLOOKUP, SUMPRODUCT, IF STATEMENT, Nested, etc... Couldn't get it to work.


My fees are as follows:
$5.00 Minimum / 1-2 Pieces
$2.00 Per Case / 3-10 Pieces
$20.00 Max / 11+ Pieces

In column B under Pieces are the quantity of the order.
In column C under Charges is where the result of the formula needs to be placed (Cost), highlighted in yellow is what the costs should result to.
In Columns E, F & G are the fees with price breaks.

Also you will see orders with 0 cases, so the charge will be of course $0 for those but we still have to report those as well.
For some reason the attachment feature is not working so I'm copying and pasting a sample of the data here so you can copy and paste to a worksheet.
Also attaching an image of how it should look:

Order# Pieces Charge Pieces Fee
BDS0535 9 18 1-2 $5.00 Minimum
ARW1020 10 20 3-10 $2.00 Per Piece
PRI1744 5 10 11+ $20.00 Maximum
BDG1217-RESTOCK 0 0
TDPC0059 1 5
MVI0094 13 20
CRC0079 3 6
SOT0437 2 5
TSV0012 1 5
TSV0013 4 8
LOC0197 2 5
ACT0060-RESTOCKED 0 0
ACT0061 15 20
ACT0062-RESTOCKED 0 0
ACT0063 11 20
ACT0064-RESTOCKED 0 0

Sample tiered pricing.PNG

Again I appreciate any assistance.

[mehmetcik]

Paste in C2 and fill down.

Please Login or Register  to view this content.

[ToroMata]

Thank you mehmetcik but unfortunately that didn't work... C2 to C17 were all given 20 as the charge, except for C11 which gave an 8 and that is correct.
Technically C3, C7, C14 & C16 are also correct with 20 but it might just seem that way because most of the cells were given 20 as charge.

The formulas I had tried produced the same result with all 20's.

[ToroMata]

Paste in C2 and fill down.

Please Login or Register  to view this content.

I attempted to follow thru with your formula and made some changes but I still can't get it to work... But it looks like I'm getting closer.
Also I made some changes to the spreadsheet to clarify better the fees section, maybe I wasn't clear with how that applies.
And added numbers to the cells where I had 0. I attached the spreadsheet.

This is the formula I was trying out: =IF(B2<2,5,IF(B2<11,(B2-11)*2,(B2-11)+20))

[Phuocam]

Try this ...

=IF(B2-2<=0,5,IF(B2-11>=0,20,B2*2))

[JohnTopley]

Try

=IF(B2=0,0,IF(B2<=2,5,IF(B2<11,B2*2,20)))

[ToroMata]

Thank you Phuocam, that worked great! I see what I was missing.

[ToroMata]

Thanks John for your reply but what Phuocam gave me worked great.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.