Excel math trick

[Steve Lenaghan]

I need Excel to do a math function that I cannot seem to find in the
manuals.

I have a small answering service that charges extra if a customer goes over
a certain limit of calls.

What I need is a formula that will allow me to put the actual number of
calls in a cell and provide a result in another cell ONLY if the number
exceeds a defined value.

Now this becomes slightly more complicated. We have two levels of extra
charges. The first is actual messages recorded in excess of a preset value.
The second is the number of calls received that exceed the previous preset
value.

Where this becomes difficult is when both numbers come into play.

There are situations where a customer has calls in excess but no messages in
excess and occasionally both occur. In this situation the calls in excess
are calculated after the message in excess since the answering computer
supplies only two numbers. The total calls and the total messages. The
message count is part of the call count. Clear ???

Anyway its probably a simple matter and if I was still using 1-2-3 I could
work out a formula, but Excel has lost me.

TIA

Steve L

 

[Randall Roberts]

this is the basic formula you are looking for you can modifie it to
fit your needs

A1 has the total number of calls
in A2 type the following

=IF(a1>40,A1-40,0)

change the 40 to the preset number of calls you are using
and it will give you the number of calls over the preset value

Randall

 

[Steve Lenaghan]

Thanks.

The first formula =IF(I23>75,I23-75,0) works fine

the second formula =IF(L23>I23,L23-I23,0)

which I changed to work off the results of the first seems to work fine too.

Should have figured this out years ago.

Steve L.

 

[Steve Lenaghan]

The second formula should work against the first and its giving me bogus
numbers.

Steve L.