Cost Per Page Countif formulea

[RobDazey]

Hello,
My hospital in South Georgia - US - chages .97 cents per medical record page from 1 -20
.83 cents per medical record over 21 pages to 100 pages
.66 cents per medical record page for anything over 101+

What I'm trying to figure out is an formulea for placing one number (page amount) and giving the total cost to the patient for all the pages copied.
If the total pages was 120 then I would want to total that cost .97 cents x 20 pages plus (+) .83 cents for the next 80 pages and .66 for the remaining pages (20) and get one cost.

If this is not possible with an countif formulea, would you steer me in the right direction.

Thanks in advance
Rob

Configuration: Windows XP Internet Explorer 7.0 excel 2003</PRE>

 

[xld]

Try

=A10*0.66+MAX(A10-20,0)*0.17+MAX(A10-100,0)*0.14

 

[xld]

Here is another way

=SUMPRODUCT(--(A10>{0,20,100}),(A10-{0,20,100}),{0.66,0.17,0.14})

 

[RobDazey]

Thank you for all the help and the quickness - ROB

 

[sanrv1f]

another one,

Excel Workbook
	A	B
1	Pages	Charge
2	120	99
3	100	85.8
4	99	84.97
5	85	73.35
6	25	23.55
7	15	14.55
Sheet4

Cell Formulas
Range	Formula
B2	=(A2-100)*0.66*(A2>100)+CHOOSE((A2>100)+1,(A2-20),80)*0.83*(A2>20)+CHOOSE((A2>20)+1,A2,20)*0.97

 

[RobDazey]

You all are so amazing - Thanks - ROB