Nested IF for phone tariffs with free minutes/texts?

[newbie188]

Hi,

trying to write a formula to calculate phone bills based on different mobile phone tariffs with free minutes and texts. The first part of the formula is ok, i.e. add monthly tariff to (number of texts times cost of texts), etc., but some tariffs have free texts and minutes, so not all usage is chargeable. how do i only multiply the usage above the threshold? see attached.

current formula (which ignores thresholds/free minutes/texts) =IF(C7="A",(($E$2*D7+$F$2*E7+$B$2)/100),IF(C7="B",(($E$3*D7+$F$3*E7+$B$3)/100),IF(C7="C",(($E$4*D7+$F$4*E7+$B$4)/100))))

(sorry, couldn't upload mini Excel sheet0

thanks!

 

[StephenCrump]

Here's one way:

	A	B	C	D	E	F
1	Tariff	Charge	Free mins	Free texts	Call cost	Text cost
2	A	0	0	0	24	15
3	B	10	150	125	28	17
4	C	25	250	unlimited	30	0
5
6		Examples	Mins	Texts	Cost
7		C	99	42	0.00
8		A	1	1	0.39
9		B	160	1	2.80
10		C	260	100	3.00
Sheet1

Cell Formulas
Range	Formula
E7:E10	E7	=INDEX((C7-IF(C7<C$2:C$4,C7,C$2:C$4))*E$2:E$4+(D7-IF(D7<D$2:D$4,D7,D$2:D$4))*F$2:F$4,MATCH(B7,A$2:A$4,))/100