Via laterlife.com Jackie aims to particularly help those new to IT and the web to
build up knowledge and confidence, so no question is too basic. At the same time she will
cover Q&As for the more experienced user.
So if you
would like to ask a question of Jackie, why not email her jackie@laterlife.com
or if you
have discovered something which may be of interest to others in making the most of the
web, then she would love to hear about that too jackie@laterlife.com
This month, I have selected various questions
related to spreadsheets. As the most common package
is Excel, my examples are taken from this application.
Q: I run a club and use
my computer to prepare financial spreadsheets. I
often need to show subtotals for different groups. Is
there a facility in spreadsheet packages that can do this for me?
A: Although a package such as Excel is not a
dedicated database application, it has many useful functions when working with data,
including working out subtotals. To do this, select
all the data and then open the Data – Subtotals menu command. There is no need to group the records first.
As an example, if your data was
organised under the following headings:
Category
Date Of Joining
Number in Family
Subscription
you will need to answer 3 questions:
How do you want each subtotal calculated? For
example, if you were totalling each category’s details, you would pick “each
change of CATEGORY”. If you were looking at
joining dates, you would select “each change of DATE OF JOINING”.
What type of subtotal do you want? Are
you looking for an Average, Maximum, Count or Sum etc?
In which column do you want the subtotals to appear? For example, is it the Sum of each SUBSCRIPTION, or the
Count of each NUMBER IN FAMILY?
Having made these decisions, click OK
and you will find subtotals and a grand total added to your data.
You can view all the details, or
click a level of display from small boxes showing under the Cell Name box on the far left
of the spreadsheet: level 2 shows subtotals and
grand total only, and level 3 only the grand total.
To return to the original
spreadsheet, select Remove All from the Subtotals menu option.
Q: I use Excel for creating
questionnaires. How can I put the symbol of a tick
in a cell?
A: Most applications have the option to add symbols
via the Insert - Symbol menu, but Excel doesn’t offer this. So adding ticks, or degree or other symbols is rather
long-winded. However, it can be done using the AutoCorrect
option.
Firstly, open your word processing package and insert the missing symbol e.g.
v onto a page in the normal way, making sure you select it from a normal text font
(rather than Windings or Symbol etc.)
Select the tick and cut or copy it into the clipboard. Then close or minimise the document and open Excel.
Go to Tools – AutoCorrect and type a short codeword into
the Replace: box that will represent the symbol but will not be commonly used in your work
e.g. addatick
Paste the symbol into the With: box. Then make sure you click Add and OK.
Next time you want the tick to appear, type addatick into a cell and
press Enter.
Q: I need reminders to
carry out particular tasks, and wonder if I can set up Excel to do this for me.
A: Some
time ago, I supported distance learners and had to phone them every 3 weeks to check on
their progress. Within the database of names and
phone numbers, I set up a column where the word “PHONE” would appear in red
whenever it was time to phone anyone. I did this
using the IF function.The function is based on the following logic:
if
statement X is true, display message A, otherwise display message B.
A simple
example is to check entries that are above or below 50 (statement X). If the entry in B1 is above 50, the display in C1
will read OVER 50 (message A), but if it is below, C1 it will display UNDER 50 (message
B).
You
would need to type into C1: =IF(B1>50,
“OVER 50”, “UNDER 50”)
(If you
want messages A or B to display numbers and not text, the quotation marks would not be
required.)
Once the
formula is entered in the first cell, you can copy it down a column as the statement would
now relate to B2, then B3 etc. and the same rule would be applied.
[For my
phone reminder, I had to compare the date I phoned last with today’s date, and once
the gap was more than 21 days, the reminder appeared. So
that the dates updated, today’s date was entered as =TODAY()]
|
