Ok, for the first entry, excel virginity hopefully broken
by the few who would read this blog entry, we start off with an easy yet tricky
formula which, if done correctly, will really, REALLY save you time and
monotonous work. (Please note this is a
custom formula and cannot be done with the ‘automatic formulas’ on Excel)
Let’s take Sam’s Travel Agency as an example. Sam has
bookings for numerous clients, all private. We have entries of up to ten
thousand bookings in the last few months. The clients fly from Point A to Point
B, even Point C and Point D all on different flight numbers, but she needs to
bill these clients per trip, not flights. So here’s what we do.
First things first, distinguish the common element, in
this case the trips in column C,
which you’d like to bill (theoretically a service fee in this case) as a whole.
We enter the formula =COUNTIF($C$2:C2,C2)=1
This will give you the end result of True vs False for
the amount of duplicate trip numbers. One True, and the rest false for all the
other duplicates. Easy peasy, for three entries that is. For those that haven’t
lost interest, let’s use an IF statement
to calculate the service fees on multiple trips.
By simply entering an IF
statement, we’ve saved time by stating that for every TRUE (which if anyone’s
following is our singular trip and is to be charged one service fee) is equal
to a $50 service fee and can then be dragged or autofilled in the column to
give us an accurate indication of how much service fees Sam has to charge her
clients. Done and dusted. Hopefully…
No comments:
Post a Comment