Spreadsheets - continuing

SPREADSHEETS(2)
ABSOLUTE CELL REFERENCES

Calculating wages: this spreadsheet uses absolute cell references

Open a new spreadsheet in Excel and set up the cells as shown below.

Save the spreadsheet as wages.xls

Now center the heading WEEKLY WAGE SHEET across columns A to F, using the "center across columns" tool. ( ) Change the font and enlarge to 14. Save again

Add the hours in column B and centre using the alignment tool, add the rate in cell G2 and the Tax in cell G3. Save again.

To calculate Jim's Gross Wage

Jim's wage is the hours he worked X the rate per hour

	Locate the cursor in C6
	type =,
	click on the cell B6
	type *
	click on the cell G2
	hit the F4 key

The formula should look like this =B6*$G$2 Click on the green tick to drop the formula into the cell

The answer is $472.50

To "fill the formula down", click on the cell C6 and move the cursor to right hand corner near the knob. The cursor will change into a "hairline".

Now hold the left mouse button down and drag down into C12.

Click once in the cell C14 and then double-click on the AutoSum icon

Save again

To calculate the Tax Jim will pay

Jim's tax is his Gross pay X the Tax rate

	Locate the cursor in D6
	type =,
	click on the cell C6
	type *
	click on the cell G3
	hit the F4 key

The formula should look like this =C6*$G$3 Click on the green tick to drop the formula into the cell

The answer is $108.68

	Fill the formula down from D6 to D12
	Use Autosum in D14
	Save

To calculate Jim's Net pay

Jim's Net Pay is his Gross Pay less his Tax

That can be done with a simple formula in E6. Try to write it for yourself. Click in the cell E6 and start with =

Now use AutoSum in E14

The Power of spreadsheets

	If you change the Rate of Pay in the cell G2 to $15 you will quickly see how a spreadsheet saves time as it accurately recalculates all the gross pays, all the tax, and all the net wages.
	Similarly you could modify the Tax rate to say 25% in just one place, the cell G3, and all the tax and net wages can be recalculated.