Excel Tip: Quickly reverse the sign of a block of numbers

This week's tip is a nice simple one that I only discovered a few weeks back when a client needed to reverse the sign on a large block of entered data.

For those of you that might not get to the tip this week (believe it or not, Excel is not always at the top of everyone's priority list over the Christmas break!), I hope you have all had a great Christmas, and I wish you all a Happy and Productive 2014!

If you want to make 2014 even more productive, why not treat yourself to a year of Excel Email support for just £75 (or $112.50 if you prefer).

Festivities and shameless plug over! On with today's tip...

The client in question had a range of numbers in Excel entered as positive figures that to be consistent with other reports should have been negatives (it was the expenses section of a budget spreadsheet and all other group companies had set their budgets up with costs entered as negatives).

I was going to enter a formula in a blank area of the spreadsheet (e.g. =-A1, where the range to be reversed starts in cell A1) and copy this over a range the same size as the range in question. I could then copy this and paste as values over the original range. I then stopped myself and wondered if it could be done in one step. A quick look around in the Paste Special dialogue box confirmed it could.

When you select Paste, Paste Special you are presented with the following dialogue box:

It is the section entitled "Operation" that caught my eye. This takes the pasted number and applies the selected operation to that and the existing contents of the cell.

So all we need to do is:

• enter -1 in a blank cell
• copy this cell to the clipboard
• highlight the range of cells that we want to reverse
• click Paste Special
• Select Multiply under Operation and click OK

Nice and simple. It also has the added advantage that it will leave any formulae intact in the destination range (this would not have been the case in my first approach).

OK, back to the celebrations, see you in 2014!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Partridges, Pear Trees and Merry Christmas!

Last week's post caused a bit of a stir - and distracted a lot of people from work in the run up to Christmas!

In case you missed it, here is the post:

A Christmas Excel Challenge

I asked readers to come up with ways of using Excel to calculate how many gifts are in the Christmas song, The Twelve Days of Christmas, and, boy, did you rise to the challenge!

You can read all of the comments on the post itself, but I have highlighted below some of my favourites.

Just for clarification, there was no trick, each days gift was considered as one gift, e.g. a Partridge in a Pear Tree is one gift (not one Partridge and one Pear Tree). However, each gift (apart from the last) is received on more than one day, the Partridge in a Pear Tree being received on each of the 12 days, the two Turtle Doves being received on the each of the last 11 days, etc.

For most people (myself included) the maths skills stopped at coming up with a formula for each day's presents:

=(B1+1)/2*B1

where B1 contains the day number in question. Therefore on the 12th day 78 gifts are received.

However, Simon Thacker was the first to come up with one formula for the total number of gifts:

=B1*((B1+1)*(B1+2))/6

returning 364 total gifts over the 12 days.

This is a Tetrahedral number from Pascal's Triangle, as described in this video from Mary Pat Campbell explaining the formula above.

No doubt that this is the most efficient solution, with just one formula to calculate the answer.

This was, however, an Excel problem rather than a maths problem, so I think it is also worth mentioning a more Excel-based solution from Ray Andrews:

"I just like the simplicity of numbering 1 to12 in row 1, 12 to1 in row 2, multiplying row 1 by row 2 in row 3 and summing the result of row three in the thirteenth column"

This uses Excel's capabilities while keeping the maths really simple.

Finally I did say that I would mention quirky solutions too and my favourite is this from Mike McCormick:

=COUNTIF(range,"*Partridge*")*1+COUNTIF(range,"*Turtle*")*2+COUNTIF(range,"*French*")*3+COUNTIF(range,"*Calling*")*4+COUNTIF(range,"*Golden*")*5+COUNTIF(range,"*Geese*")*6+COUNTIF(range,"*Swans*")*7+COUNTIF(range,"*Maids*")*8+COUNTIF(range,"*Ladies*")*9+COUNTIF(range,"*Lords*")*10+COUNTIF(range,"*Pipers*")*11+COUNTIF(range,"*Drummers*")*12

Where range contains the lyrics. of the song!

Thanks for all of your contributions.

Have a wonderful Christmas and a fantastic New Year!

Glen




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

A Christmas Excel Challenge

Just a short, but seasonal, post this week - prompted by a quiz question my daughter was asked at school.

The question was, "How many gifts were received, in total, in the Christmas song The Twelve Days of Christmas?"

I thought that would make for an interesting Excel challenge.

So, the challenge is this:

Use Excel to answer the question, "How many gifts were received, in total, in the Christmas song The Twelve Days of Christmas?"

Explain in the comments how you would do this, remember though that it would be good practice to treat the 12 as a variable - so that you could enter any number of days of Christmas and the spreadsheet would calculate the total number of gifts.

In case you don't know the song, here are the lyrics:

On the first day of Christmas
my true love sent to me:
A Partridge in a Pear Tree

On the second day of Christmas
my true love sent to me:
Two Turtle Doves
and a Partridge in a Pear Tree

On the third day of Christmas
my true love sent to me:
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the fourth day of Christmas
my true love sent to me:
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the fifth day of Christmas
my true love sent to me:
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the sixth day of Christmas
my true love sent to me:
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the seventh day of Christmas
my true love sent to me:
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the eighth day of Christmas
my true love sent to me:
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the ninth day of Christmas
my true love sent to me:
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the tenth day of Christmas
my true love sent to me:
Ten Lords a Leaping
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the eleventh day of Christmas
my true love sent to me:
Eleven Pipers Piping
Ten Lords a Leaping
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

On the twelfth day of Christmas
my true love sent to me:
12 Drummers Drumming
Eleven Pipers Piping
Ten Lords a Leaping
Nine Ladies Dancing
Eight Maids a Milking
Seven Swans a Swimming
Six Geese a Laying
Five Golden Rings
Four Calling Birds
Three French Hens
Two Turtle Doves
and a Partridge in a Pear Tree

Good luck. Post your approaches in the comments, and I will feature the slickest and/or the quirkiest solutions in next week's post, along with my own solution.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Show titles on every page of a report

Before we get into today's post, I just want to give you a quick update on an earlier post.

A couple of month's back I mentioned that I was writing a publication for the Institute of Chartered Accountants in England and Wales (ICAEW) on automating management accounts in Excel. A number of you asked to know when the publication would be available.

Well, it is now. Members of the ICAEW IT Faculty or Excel Community have already received a hard copy in the post.

At the following link you can either purchase a copy (from the ICAEW) or join the ICAEW Excel Community and then download it free if charge:

Automating management accounts production in Excel: a simple approach

OK, on with today's post. Many Excel spreadsheets are written with the intention of the output being printed. This sometimes causes a challenge when the spreadsheet spans multiple pages and we want the titles to appear on every page. Well, Excel has a solution (CLUE: It's not to break the sheet up into pages and enter the titles at the top of each page).

On the Page Layout ribbon, click on the Print Titles icon.

(In earlier versions of Excel, you can get to the same screen by choosing File, Page Setup and selecting the Sheet tab).

You will see the following window:

In the Print titles section, you can specify the rows you want to repeat at the top of every page by clicking in the "Rows to repeat at top:" box and selecting the rows that include your titles.

As you can see there is also a box to allow you to repeat columns if your print will straddle multiple pages horizontally. This is much less common, but can sometimes be useful.

Note that neither setting changes the spreadsheet that you see on the screen - just what is printed.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Painting Yoda in Excel!

Obviously we all know how useful Excel is as a tool for work, but every now and again I come across a spectacular and unusual use of Excel, that I want to share with you.

Back in June, I posted some examples of the Excel art of Tatsuo Horiuchi.

This week I came across another amazing Excel artist by the name of Shukei. You can see some of Shukei's work on the YouTube channel Shukeiart.

In the video below, you can see the whole process of creating an Excel painting of Yoda from Star Wars condensed into under three minutes!




Now that makes using Excel to create next year's budget look pretty tame!

May the force be with you!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".