Excel 2000 math question

[Steve Lenaghan]

1- I use a formula =IF(L67>75,L67-75,0) to calculate extra billing charges
in excess of 75 units. This works fine.

2- I have a second formula =IF(I67>(L67),I67-(L67),0) to calculate charges,
at a different rate, above the result of the first formula. This formula
works fine if the result of formula 1 is >0, however if the result of
formula 1 is 0 I get an undesirable figure. I've been playing with the
formula but I haven't done this in 15 years and am lost.

TIA

Steve L.

 

[Dave R.]

Steve, what cell is formula 1 in?

you have not described how you want the 2nd formula to work, what it should
return, etc, or why the result u get is undesireable (or what it is).

 

[Steve Lenaghan]

Column L is value input by book keeper
Column M is first formula
Column N in dollar amount derived by the formula

Column I is value input by book keeper
Column J is second formula
Column K is dollar amount derived by second formula.

Conditions are...
If value L is =< 75 then N should be zero
If value L is >75 then N should be a number in excess times the billing

This formula works just fine

Conditions for the 2nd formula are...
If value I is <= 75 then K should be zero
If value I is >75 then K should be a number in excess, times the billing
If value L >75 then value I in formula two should be reduced by the value L
in excess of 75
The value I should remain displayed as entered.

This is a two tiered usage charge.

a- The customer receives a fixed call allowance i.e. 75 calls
b- The customer is charged $0.25 for each message in excess of the fixed
call allowance (75)
c -The customer is charged $0.10 for each call answered in excess of the
fixed call allowance (75) or b- (+75) whichever is the larger number.

My problem is that if N returns zero then J returns a value of (I-L)

Does this make sense. My accounting package can't do this so we have
developed a spread sheet. Till now its been a manual calculation but we
plan on using a less skilled operator to do initial data entry. What
complicates the logic is that the answering computer software gives a column
with the total calls received followed by a column with the messages
recorded. Unfortunately at the moment we cannot import a file from the
answering software as the two networks we use are not compatible. (future
project)

Ideas ???

TIA

Steve L.

 

[David]

Does this make sense.

No!

You say:

My problem is that if N returns zero then J returns a value of (I-L)

Yes, it will, because that is what you have programmed in! So what do you
want in column L?

Perhaps you could give a couple of examples (showing each possible case) of
some sets of numbers (in L and I?) and the results you want to be produced
by the formula in Column J, and then I am sure that we can help you.

Regards

David

 

[RWN]

Steve;
Don't know if you've rec'd an answer somewhere else but seeing as I
don't have a life and need some mental exercise I thought I'd give it a
go.

Firstly, my response is based on what I could understand from question.

Obviously (if I've got this right!), for a message to be Taken the call
has to be Answered, therefore the calls Answered would be greater than
or equal to the messages Taken.
It appears as though you are trying to compute service charges based on
both calls Answered in excess of 75 and messages Taken in excess of 75.
However, if the user is surcharged for messages Taken then the
surcharged calls are subtracted from the calls Answered surcharge.
Therefore (I'll use the names Answered and Taken to try and keep it
straight in this old head of mine!) ;

Cell TakenSchg (Col"M")
=If(Taken>75,Taken-75,0) (I believe this is what you've got)

Cell AnsweredSchg (Col "J")
=If(Answered>75,Answered - 75 - TakenSchg,0)

Excuse the verbosity of my reply, it's just that I've found that most
problems are result of lack of understanding of what the problem is.

--
Regards;
Rob

Please reply to the NG, I'm already up to my eyeballs in Nigerian/South
African get rich letters
as well as "Microsoft Critical Updates" et al.