Excel Calculation?

Question

Hello,

I am having a doubt in Excel calculation. I have tried to do the following calculation in excel. It is a telephone calculation. Two equations are there in this calculation.

EQ : 1 - if the no. of calls is upto 300 then the charge will be

A = (no. of calls*amount1)
B = A*Service Tax
C = A + B + Education Cess and It will be the total payable amount.

EQ : 2 - If the no. of calls is above 300 then the charge will be

for the first 300 calls same as EQ 1 and the remaining 100 calls

A2 = (no. of remaining*amount2)
B2 = A*Service Tax
C2 = (A + B + Education Cess)+EQ 1 will be the answer

Can anybody assist me.

Answer 1

I used four cells to get the total payable amount. In A1 the number of calls are entered. I tested using 200, 299, 300, 301 and 600.

I assumed a few things:
Amount1 is 10 cents a call
Amount2 is 7 cents a call
Service Tax is 5 percent of the calls charges
Education Cess is a flat 15.00.

The formulas for A2 and A3 are below. A2 calcuates the charges for calls up to 300. A3 calculates the charges for calls more than 300. Cell A4 just adds the two charges from A2 and A3.

=IF(A1<300,
(A1*0.1)+(A1*0.1*0.05)+15,
(300*0.1)+(300*0.1*0.05)+15)

In the above formula, if calls are < 300, it calculates against A1, if they are 300 or greater, it calculates against only 300.

=IF(A1>300,
((A1-300)*0.07)+
((A1-300)*0.07*0.05)+15,0)

In this formula, we calculate against calls minus 300 and enter zero otherwise.

In Cell A4 put =A2+A3

This will show you the subtotals and the total so you can see if they seem right.

Answer 2

1. Following is your solution.
2. Make sure you insert a comment to the first cell to explain what was done - you will not remember it in two months and this will be a problem if you want to change something and do not know what.
3. I have used generic values, replace them with the actual numbers:
Number of calls >> Cell A1 is used
Amount1 = 1.01
Amount2 = 1.02
Service Tax = 3% >> Represented as 1.03
Education Cess = 4% >> Represented as 1.04
4. I added extra spaces so that you could copy and paste the formula; Excel will ask whether to correct those when you will paste it - so click Yes.
5. After checking it works correctly, please reward my time and effort with a Best Answer.
6. Voila:

=(MIN (A1, 300) *1.01 *1.03 *1.04) + (MIN (1, TRUNC (A1/301 ,0)) * (A1-300) *1.02 *1.03 *1.04)