This blog looks at the Excel function SUBTOTAL and how you might find it useful:
The SUBTOTAL function has two parts within the brackets: the second part is the easy bit - the range of cells you're using; the first part is what (out of 11 options) you want to do with the contents of that range of cells, e.g. add them together (number 9), average them (1) or count them (2 or 3) - the full list is below.
So, if I wanted to use it to sum the cells A3 to A40, I could create the formula =SUBTOTAL(9,A3:A40) instead of using AutoSum to create =SUM(A3:A40)
So, why (I hear you ask) would I want to create a longer formula to do the same thing as SUM? Because it's not quite the same - here's a couple of benefits of using SUBTOTAL:
- If I have a single column of numbers intermingled with sub-totals and use =SUM to create a grand total at the end, the grand total would add the numbers and the sub-totals together (for example, if I had the numbers 1 to 4 in B3 to B6 and again in B8 to B11 with sub-totals in B7 and B12, a total of the column would be 40 instead of the correct 20), whereas using the SUBTOTAL option would ignore the subtotals and only add the typed-in numbers together.
- This second reason is more useful for me: when I filter a list with a SUM at the bottom, the sum still shows the sum of all numbers (hidden and visible) - SUBTOTAL just shows the sum (or any function I've chosen instead of sum) of the visible rows.
Here are the 11 function numbers (the first part within the brackets) for SUBTOTAL:
- 1 - AVERAGE
- 2 - COUNT (counts all cells with numbers in them)
- 3 - COUNTA (counts all cells with contents - numbers or text)
- 4 - MAX (largest number)
- 5 - MIN (smallest number)
- 6 - PRODUCT (multiplies all of the numbers by each other)
- 7 - STDEV (estimates standard deviation)
- 8 - STDEVP (calculates standard deviation based on the entire population)
- 9 - SUM
- 10 - VAR (estimates variance in the specified range)
- 11 - VARP (estimates variance based on the entire population)
If you use the AutoSum button with a filtered list, it will automatically create a SUBTOTAL(9, function instead of a SUM - I find this a useful time saver.
One final thing: for staff and students at the University of Bath, we're offering Excel workshops, where you can book a place and then bring your workbook(s) along (or log into one of the training PCs with your normal Bath username) to see how we can help resolve any improvement requirements, issues, niggles, hassles or problems. For more information, click here then select Tutor Led Training, then Excel: Workshop to see the dates/book a place.
Bye for now,
The roll-out of Skype for Business (Skype) to all staff is going well and will be completed before we break for Christmas this year. Over the coming weeks staff who are not already using Skype will receive 2 emails, one before their go live date and one on the day. We are encouraging staff to send an instant message as soon as they can so they get used to using Skype as soon as possible. Floor walkers from Computing Services will be available to help you open and send your first instant message.
Find out more
Sign up for our training course to explore the different ways you can use Skype for work and research. Use our guide to accessing Skype on your work computer and your own devices.
If you have any queries about Skype for Business please contact the IT Service Desk.
We are aware that some users have received a phishing email with the subject line: Deactivation Request. We have made steps to prevent further instances of this email being received.
If you receive one of these emails, delete it and do not open the link. If you opened the link and completed any information then please get in touch with our Service Desk at go.bath.ac.uk/it-help-form.
Read our advice on phishing emails.
Example content of the phishing email
Subject: Deactivation Request
We received a request to terminate your Email and process has started, kindly give us 24.00 hours to complete your request.
If you did not make this request Please here: undo deactivation request [LINK]
All information on your account will be deleted and access to your online access will be denied.
You may be having an issue printing due to an issue with one of the print queues.
To resolve this issue, you will need to change the default printer on your PC to the PostScript print queue.
How do I change my default printer?
To change the default printer to the PostScript print queue:
Select Devices and Printers
Right click on the printer called PostScript on MyPrint
Select Set as default printer
Printing from Microsoft Office or Acrobat
If the PostScript on MyPrint or \\MyPrint\Postscript printer does not appear as the printer:
Click on the down arrow on the printer selection box
Select PostScript on MyPrint or \\MyPrint\PostScript
Why is this happening?
There are two main queues within the Managed Print Service – General and PostScript. There is currently an issue affecting the General print queue and a resolution is being investigated.
Thank you for your patience whilst a resolution is investigated.
For all other issues please log a ticket http://go.bath.ac.uk/it-help-form
If you are a regular follower you might be familiar with our IT status blog which details whether the services we look after are operational or are undergoing maintenance.
We wanted to create a clearer way of providing this information so that you can tell quickly if there are issues with a service we provide.
Introducing Status.Bath – a dashboard that works using a traffic light system and easy to understand phrases. This will launch 16 November 2016.
The IT Status blog will no longer be updated and you will need to bookmark status.bath.ac.uk to keep up to date with the status of our IT services.
How it works:
- Operational – written in green this is good, the service is working.
- Partial Outage – written in amber this means something has gone wrong with part of the service (for example, if the Wi-Fi is unavailable in a particular building but not the entire campus).
- Major Outage – written in red, this means the service is unavailable.
- Maintenance – written in grey, this is planned maintenance (for example during our maintenance period, normally 7am-9am on Tuesdays).
Check it out now and see what you think. We welcome your feedback, please fill out the survey and let us know what you think.
We are aware that some users have received a phishing email with the subject line: Parking Charge Reminder Number [a number]. Please delete this email. It is a convincing looking email and it claims to be from UK Parking Control Limited (UKPC) which is a legitimate company. Please note that this company never sends out parking charges by email. The phishing email is not from UKPC.
The bottom of the email asks victims to click on “payment options and photos” for more information - a link that is likely to contain malware. If you receive one of these emails, delete it, do not download the attachment. If you did download the attachment please get in touch with our Service Desk at go.bath.ac.uk/it-help-form.
Example content of the phishing email
Reminder To Ticket Keeper
Parking attendant №: 74292 had liable reason to believe that the following misdeed of the terms and conditions of parking happened on our client's private land (Details of which were clearly and prominently displayed and agreed to by the driver by the act of parking the vehicle).
Parking Charge Information
Parking Charge Date:
Parking Charge Amount:
14 Day Early Pay Discount:
Discount You Could Have:
Total now due: £90.00
The IT Shop will be closing at 3.30pm on Friday 11 November 2016 due to staff shortage. It will open as normal at 9am on Monday 14 November.
Thank you for your understanding.
Hello fellow surfers of the blogosphere!
As this is my first blog post, it seemed appropriate to introduce you to my team, Business Support Systems, and to tell you a little about what we do.
The Business Support Systems team within Computing Services is still a relatively new team, only becoming complete in February 2016. Our mission is to develop solutions to Business Process Management needs across the University, in line with the larger Process Improvement initiative that the University is running.
If you're not sure what Business Process Management is, it could be described as 'improving the way that data flows throughout an organisation, which may or may not use technology to do so'.
We are most effective when you come to us with your Business Needs or Problems, rather than asking for a specific solution - we have the expertise and experience to help you meet your aims by working with you to build the right solution. If you work collaboratively with us to do this, our solutions will add value to the organisation by supporting improved, streamlined processes which add value to the business and specific teams or groups of customers. Our solutions allow people to focus on making appropriate decisions and human interactions, rather than being a slave to out-dated procedures and archaic ways of working.
Some examples of solutions we that have delivered so far are:
- TRAD - 'Terrorist-Related Access to Data'. This is a register for staff and students to log their valid need to access data and information which might appear to support terrorism. This is part of the Univerity's response to the Government's PREVENT initiative to try to stop the radicalisation of young people.
- CCR - the 'Central Contracts Repository'. The CCR acts as a central repository for storage and basic management of contracts between the University and third parties. The solution helps departments and teams to easily store and retrieve their contracts and manage renewal reminders within a system accessible by legal and procurement teams with responsibility for best practice in these areas.
- CSMF - 'Computing Services Management Forum'. The CSMF tool allows team leaders and managers within Computing Services to write and share monthly reports for wider consumption and for members of the forum to mark items for discussion during our monthly department meeting. This has greatly improved the efficiency of our department meeting (reducing them from over 1 hour each to an average of 20 minutes), whilst still allowing a frank and clear exchange of information within the department.
If you want to contact the team, you can email us at email@example.com, or by raising a ticket for us. Just make sure to mention BSS at the top of your description and it'll get to us.
I'll be blogging more from now on, telling you about the team, what we have achieved, what we are working on and what ideas we have for the future.
Until then, just remember:
“The first rule of any technology used in business is that automation applied to an efficient operation will magnify the efficiency. The second is that automation applied to an inefficient operation will magnify the inefficiency.” - Bill Gates
With the successful introduction of Business Objects Webi the old Deski client is no longer needed and will now be removed from any University desktops that still have it from 14 November.
The software will notify you of removal and of a required restart.
If you have any queries or concerns regarding this change please contact IT Support via the self-service portal who will be able to advise.
We're pleased to let you know that Microsoft have now released a Mac client for Skype for Business. This replaces the existing 'Microsoft Lync' client that was previously available. This client takes a huge step in bringing parity between our Windows and Mac estate in terms of capability and now, more than ever before, our Mac users can experience the same level of integration with the University's Communications & Collaboration infrastructure as our Windows users do.
If you have 'Self Service' on your Mac then you can download the Skype for Business client directly, by clicking on the Self Service icon in your dock and selecting 'Install' under 'Skype for Business'.
In addition, staff already using Skype for Business at the University can install Skype for Business on your personally-owned Mac. Once installed, open the application and enter your University credentials as follows, replacing 'abc123' with your own username. You can find your username on your library card:
- Email address: Staff should enter 'firstname.lastname@example.org' where as for students it's 'email@example.com'
- Password: Your University password
- User Name: Enter 'campus\abc123'.
Find out more about Skype for Business being rolled out to all staff at the University on our blog post.