Excel aata hai kya?

Status
Not open for further replies.
linardni

linardni

Journeyman
Hi...can anyone help me with the following problem of excel. Please. Just tell me what formula is to be inserted in cell F2.
1) calculate total calls.
2) Calculate Amount per connection as Rs.0 for first 500 calls, Rs.1.00 per call for next 300 calls, Rs.1.50 per call for next 400 calls and Rs.2.00 per call for rest if nature of connection is "Domestic", but if it is "Business" type charge will be different. Nil for first 500 calls, Rs.1.20 per call for next 300 calls, Rs.1.50 per call for next 400 calls and Rs.1.75 per call for rest.

Tel Opng Call Clsng Call Total Nature of Conn Bill Amnt
22211285 128991 142561 Business
24035546 13948 14753 Domestic
24034242 17989 19159 Domestic
22102193 33738 34158 Business
24785546 38906 39933 Domestic
Service Tax @ 10.2%
Rental Charge Rs.400 per connection
Net Total
 
godsownman

godsownman

Padawan
Please be clear your problem cannot be clearly understood.

Please if possible post a screenshot of the excel table.
 
I

ishaan

Padawan
this is what u hafta do, from scratch.

make a subheading on top - domestic.
make 2 colums - CALLS and COST.
make the rows of diff call charges.
then make a row called total domestic calls.

agn, make a subhead - business.
make 2 clomuns - CALLS n COST.
make the rows of diff call charges here also.
then make a row total business calls.


then, as u use the phone, keep updating itunder the CALLS place.

in the COST place, put the formula for the cell of the CALLS multiplied by its cost.

do this for all of em

then in the end make a TOTAL CHARGE row

and put a fucntion for the SUM of the total of each business and domestic.

seems pretty complicated if u want i can make one for u n send it.


------------------

sorry...while i was riting this u edited ur post so i din c da bottom part. its kinda tuff 2 understand cud u upload the file somewhere so v can dload n c ?
 
QwertyManiac

QwertyManiac

Commander in Chief
use www.imageshack.us to upload it pics and post its link here with/without the
 
S

sakumar79

Technomancer
Create two extra columns (which u can hide/protect if necessary) that track number of business calls upto that row. Ie, if X and Y are the columns, in the 10th row, if value in col E (E10) is business, X10=X9+D10 and Y10=Y9, else, X10=X9 and Y10=Y9+D10. Then, look at E10 along with X10 or Y10 (IF E10 is Business then use X10 for number of calls made, else use Y10 for number of calls made) and calculate cost of that call. Proceed this way till the last line, then add basic cost and then service tax...

U might want to put a copy of the total somewhere at the top to make it easier to read without having to scroll.

Hope this helps.
Arun
 
OP
linardni

linardni

Journeyman
sorry buddy...i want some formula to be inserted in the cells of F Column so that the result is automatically deducted.....i want to use logic function to solve it.
 
A

amit_stg

Broken In
i dont think this can be solved just by typing a single function is f column for the entire calculation, you will need more no of columns (may be hidden) because this type of calculations needs mare than one calculated values.
 
OP
linardni

linardni

Journeyman
@vignesh
Hi...i dont think u will have difficulty in understanding the problem of excel. however i am once posting it for u....plz try
1) calculate total calls.
2) Calculate Amount per connection as Rs.0 for first 500 calls, Rs.1.00 per call for next 300 calls, Rs.1.50 per call for next 400 calls and Rs.2.00 per call for rest if nature of connection is "Domestic", but if it is "Business" type charge will be different. Nil for first 500 calls, Rs.1.20 per call for next 300 calls, Rs.1.50 per call for next 400 calls and Rs.1.75 per call for rest.
 
S

sakumar79

Technomancer
Please note that using one formula will not be possible because you need to keep track of the number of personal and business calls at each stage.

What I had suggested in my earlier post is basically logically driven. Please read my post carefully and you will notice that while I have written the procedure in words, when you incorporate them into Excel, you will be working with a lot of IF statements and some nested IF statements also...
For example, where I have said "if the value in col E (E10) is business, X10=X9+D10 and Y10=Y9, else, X10=X9 and Y10=Y9+D10", you should be entering in X2 something like "=IF(E2="Business", X1+D10, X1)" and for Y2 something like "=IF(E2="Business",Y1, Y1+D10)". This way, you will have a formula that you can copy and paste to the rest of the records below. In similar fashion, you can convert my algorithm into logical statements.

Hope I have clarified my earlier post.
Arun
 
Status
Not open for further replies.
Top Bottom