Tiered pricing issue

**dea397732** · 06-04-2010, 12:40 PM

Well I thought I had this entire problem figured out utilizing numerous nested IF statements however I quickly began to find holes in the solution and realize I need some help.

The setup is simple...I am tallying our print users pages printed totals on a monthly basis. Users are given 750 pages for free, pages 751 to 1500 are charged at $.03 and 1501+ are charged at $.05.

My original plan was along the following lines, where H2 is year to date total and I2 is the current month:
=IF(H2>1500,I2*0.05,(IF(H2 > 750,I2*0.03,(IF(I2 < 1500,IF(I2 >750, (I2-750)*0.03,0),((I2-1500)*0.05)+22.5)))))

I'm not sure how to account for those users that say have 50 pages in January then end up with 1800 in February, so that they get charged the $22.50 for those 751-1500 plus the approx $15 for next 300 pages. Thanks for any leads!

**Ron Coderre** · 06-04-2010, 01:14 PM

With
H1: YTD_Pages
H2: a YTD count....eg 751

I1: CurrMthPages
I2: a Curr Mth count...eg 700

J1: Price

and...these values in M1:P4

Over       Thru    Rate  Incremental
0          750    $0.00        $0.00 
750       1500    $0.03        $0.03 
1500  Infinity    $0.05        $0.02

This formula returns the Curr Mth Price:

J2: =SUMPRODUCT((H2>$M$2:$M$4)*(H2-$M$2:$M$4)*$P$2:$P$4)
-SUMPRODUCT(((H2-I2)>$M$2:$M$4)*((H2-I2)-$M$2:$M$4)*$P$2:$P$4)

In the above example, the formula returns: $0.03
because during the current month, the user crossed the $0.03 threshhold by 1 page.

Is that something you can work with?