Convert formula to its results

Tools: Paste Special

Level: beginner

Problem

From time to time you will have to save the result of a formula, and sometimes more than a single formula, but rather an array of them.

Someone sent you a range of data that you need to label with line numbers to use as a database. Dragging a fill handle for 1000 lines is a difficult and error-prone solution, so you wrote a simple formula and copied it across the range to create numbers from 1 to 1000. But if you try to sort this table in any way other than its original order, the line numbers will change. How do you make these numbers permanent?

	A	B
		Item1
	=A1+1	Item2
	=A2+1	Item3

	=A999+1	Item1000

Solution

Select the A1:A1000 range.
Copy.
Click Paste. Alternatively, you can select Paste Special.
Select Values.
Hit Enter.

The formula results will get pasted right over formulas. Mission accomplished.

Similar tasks

Paste Special is a highly flexible and functional command. Among other things, it can let you do the following:

Paste column widths. This command helps a lot if you want several columns to be of same exact width as a certain column.
Transpose a matrix. This command will rearrange the data in a matrix so that rows become columns and columns become rows.
Paste with math operations. You got from someone data where percentages are expressed as 25 instead of.25. After formatting it as "%", you will see "2500%" where "25%" is supposed to be. You will appreciate the possibility of instantly dividing the whole range by 100 without creating an additional range of divided data, saving it as values and pasting it back. Type 100, cut the cell and Paste Special it to this range with a "divide" switch on.

A legible formula

Tools: range names

Level: beginner

Problem

You need to calculate employees' social security contribution. The company pays to social security a fixed fee of 25 dollars per employee per year plus a 6.25% of salary earned during this period.

	A	B
	Fixed fee
	Social security rate	6.25%
	Employees	Salary
	Jack Sparrow
	Elisabeth Swann
	Will Turner
	Total SS contribution	=$B$1+($B$2*SUM($B$4:$B$6))

To understand the formula you will need to look up what is in cells B1, B2 and range B4:B6. As formulas and data sets get bigger, it will be harder and harder to understand the code.

There is a way to make a formula more legible by using names.

Solution

Names are essentially text shortcuts that are assigned to a cell or range in your workbook. Names can also refer to formulas but we will not need this feature so far, even though it will prove very useful later on. There are at least three methods to create names from workbook data.

Active cell B1.
Find a Name Box of a formula bar. It is by default the first box from the left and it shows now B1.
Click in Name Box.
Type ss_fee.
Press Enter.

You have created a name ss_fee that stands for whatever is entered in cell B2.

Next, you can use a Define Name command. It is available from Insert-Name-Define menu (03) or Formula ribbon tab (07/10).

Active cell B2.
Use Define Name command.
In the dialog that opens, type ss_rate.
Press Enter or click OK.

And finally, you can create a name from a text string cell automatically. For this, we will use Create Name menu command (03) or Create from Selection ribbon command (07/10).

Select range B3:B6
Use Create Name / Create from Selection command
Make sure there is a selection check mark in Top Row field.
Press Enter or click OK.

Range B2:B4 is now named Salary. Note that B3 which actually contained the name is properly excluded from range and will not mess with calculation. If you change the column header, the range name will not change until you redefine in, so do not count on this in your formula writing endeavors.

Now let's use the newly created names in the formula. There are also several ways to do so.

Active cell B7.
Type =
Press F3.
Select ss_fee from the dialog that opens.
Press Enter or click OK.
Type +(
Use menu command Insert-Name-Paste or ribbon command Use in Formula
Select ss_rate from the dialog that opens.
Press Enter or click OK.
Type *
Start typing salary. A dropdown list of names that match what you typed will appear.
Select salary from the dropdown list of names.
Type)
Press Enter.

Now your spreadsheet will look as follows.

	A	B
	Fixed fee
	Social security rate	6.25%
	Employees	Salary
	Jack Sparrow
	Elisabeth Swann
	Will Turner
	Total SS contribution	=ss_fee+(ss_rate*salary)

Similar tasks

To edit names once they are created, use Insert-Name-Define menu command (03) of Name Manager ribbon command (07/10). Name Manager is a convenient interface to edit many names at once. For Excel 2003, you can install an add-in by Jan Karel Pieterse that creates a Name Manager interface.

It is best to follow certain best practices when using names:

Names are great if not overused. If you have a variable that is repeatedly called many times over in a sheet of workbook, this is probably a good name target. If you are referring to a cell just once, better leave it as is.
Names should be brief but descriptive. By looking at a name, you should at least understand what it may mean. Names that are too abstract or too abbreviated will keep confusing you.
Groups of similar names can be identified by a prefix. For example, if you have several lists, you can start all of their names with l_ - e.g. l_units, l_prices and so on. This way, that will stick together in the list and in the Name Manager.

For other operations with names, refer to Define and use names in formulas | Online Office Help from Microsoft.

A dynamic date range

Tools: functions DATE, YEAR, MONTH, DAY, TODAY

Level: intermediate

Problem

You need to make a project timeline with monthly intervals. You do not know, however, when the project will actually start. It may be this month, next month or even next January. Given that your model will be reviewed in many meetings, you will need to update the timeline every time you take it anywhere. Worse, your boss might take it to meetings too, and you cannot rely on her updating the timeline.

How do you make a formula that automatically starts the project timeline from the first day of the month immediately following this month?

Solution

We will use several built-in Excel date and time functions. In particular, we will need TODAY(), YEAR(), MONTH(), DAY() and DATE(). For simplicity's sake, let's also number future monthly periods – 1,2,3,4 and so on and enter them in cells A6 and down column A.

Enter function =TODAY() in cell B1. It requires no arguments and only displays the current date. Excel date is a serial number. Excel considers January 1, 1900, to be day number 1, January 2, 1900 day number 2 and so on. The dates from year 2012 already exceed 40 000.
Enter function =YEAR(B1) in cell B2. YEAR returns the year of a date as number.
Enter function =MONTH(B1) in cell B3. MONTH returns the month of a date as number.
Enter function =DAY(B1) in cell B4. DAY returns the day of a date as number. You have now the today's date properly broken into year, month and day. Every day, these figures will change, as TODAY() function that they refer to will return new value.
Now, let's reconstruct the first of next month from these dynamic variables. Enter function =DATE($B$2,$B$3+A6,1) in cell B6.
Copy cell B6 down column B. You now have a range of cells in column B with future period dates.

	A	B
	Today's date	=TODAY()
	Today's year	=YEAR(B1)
	Today's month	=MONTH(B1)
	Today's day	=DAY(B1)
	Period	Period date
		=DATE($B$2,$B$3+A6,1)
		=DATE($B$2,$B$3+A7,1)
		=DATE($B$2,$B$3+A8,1)
		=DATE($B$2,$B$3+A9,1)

We could have also embedded all the intermediate calculation in the DATE() function; the only reason not to do so is make the formula more understandable at this point.

Formula syntax

In generic form, the DATE() function we used uses the following syntax and variables.

Code	Variable	Comment
=DATE(
YEAR(	Year	Year of the date for which you want a serial number.
TODAY()	No variables	We use the same year as today's date.
)		Function YEAR() complete
MONTH	Month	Month of the date for which you want a serial number.
(TODAY()	No variables	We take today's month…
+1		…and add 1 to it to make it next month. 1 is actually the period's ID, so we can substitute a fixed number with a value from cell with the project ID.
)		Function MONTH() complete
	Day	Day of the date for which you want a serial number. We use a fixed number – 1, as we want a date for 1st of every month.
)		Function DATE() complete

Note that in December our function will have the same year and month 13 for arguments. Which is incorrect (duh). Fortunately, Excel takes this into account and just advances the year by 1, and returns the month to 1 – as it should be.

Similar tasks

Task 1.

Two days later.

Solution

=TODAY()+2

We do not need a DATE functions since every day is +1 to a serial number that TODAY() returns.

Task 2.

Three months before.

Solution

=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

Task 3.

Last day of current month.

Solution

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)

A zero day will roll the date one day back from first day of month – i.e. to the last day of previous month.

Check for a condition

Tools: function IF

Level: intermediate

Problem

If the company had positive profit in a certain quarter, it should pay profit tax (30% of reported profit). If the quarter closed at a loss, no tax is due. The company had the following quarterly results (in millions of dollars):

A	B	C	D	E	F	G	H	I
Quarter
Profit			-5	-4	-6			-4
Tax

How do you compute profit tax with just one formula?

Solution

Use IF() function.

Active cell B2.
Use Function Wizard or type =IF(B2<=0,0,B2*0.3) and press Enter or click OK.
Copy formula to C3:I3.

You should now have the following table.

A	B	C	D	E	F	G	H	I
Quarter
Profit			-5	-4	-6			-4
Tax	1.2	1.2				1.5	1.8

Formula syntax

Code	Variable	Comment
=IF(
B2<=0,	Logical_test	Check if B2 contains a profit or a loss?
0,	Value_if_true	IF B2 is negative or zero, then it's a loss and condition is TRUE. In this case, return 0 – no tax due.
B2*0.3	Value_if_false	IF B2 is positive, then it's a profit and profit tax is due, condition is FALSE. Compute tax by multiplying profit by 30% and return the value.
)		Function complete.

Similar problems

Task 1.

Column A contains party guests, column B - their age. Mark in column C those guests who are and are not allowed to drink. Legal drinking age is 21.

Solution

=IF(B1<21,"No alcohol!","Can drink")

Task 2.

Column A contains sums, column B has a mark for the currency – it's either "dollar" or "euro". Convert all sums market as "euro" to dollars at an exchange rate of 1.4 dollar per euro.

Solution

=if(B1="euro",A1*1.4,A1)

Task 3.

Prevent division by zero.

Solution

=IF(A2=0,"Do not divide by zero!",A1/A2)