Telephone Bill

  • Thread starter Thread starter Kutty
  • Start date Start date
K

Kutty

I use to get monthly bill for my Company and I have to arrange the bill for
our staff as follows. How to do this in excel formula ?

If PRIVATE CALL <= $200 the calls are free for staff. The staff have to
pay to
the company for all more than $200 PRIVATE CALLS . So there are 4 coulums
as follows :
BILL AMOUNT
BUSINESS CALL
PRIVATE CALL
DUE FROM EMPLOYEE
 
If you are excepting the below mentioned answers in ColD then try this
formula in cell D2 and copy down as required

=MAX(0,C2-200)

Col C Col D
Private Due
250 50
50 0
200 0

If this post helps click Yes
 
The answer was not clear to me.

Jacob Skaria said:
If you are excepting the below mentioned answers in ColD then try this
formula in cell D2 and copy down as required

=MAX(0,C2-200)

Col C Col D
Private Due
250 50
50 0
200 0

If this post helps click Yes
Click to expand...
 
Jacob's Formula First DEDUCTS $200 from the total bill.
For those Total Bills that are LESS then 200 the result is a NEGATIVE (-100)
for (100-200) - But if the Total Bill is 300 then a positive of 100 (300-200)

Then the MAX() kicks in and says, Give me whichever is HIGHER, 0, -100
And 0 is Higher!! Then whichever id higher 0, 100 >> of course 100 is
Higher!

OK?

Choice gets the MAXIMUM of Either:
0 (Zero)
 
I would have thought it was $200 per month that is allowed, not $200
for each call.

Hope this helps.

Pete
 
Back
Top