Absolute and Relative formula

Relative Formula

This formula adds the content of the two green cells together.

If you copy and paste the formula in cell C1 into cell C2, the formula re-calculates by adding the two yellow cells together.

If you copy and paste the formula in cell C1 into cell C3, the formula re-calculates by adding the two green cells together. You will notice that an error message occurs which say “Value”. This means that the formula will not work because the data in cell A3 is not numeric.

If you want to transfer the answer shown in cell C1 (i.e. 20) to another part of the spreadsheet (e.g. here), you need to copy and paste it. But when you do this it does not work! You get zero!

You therefore need to use an Absolute Formula

The original formula in cell C1 was: =SUM(C10+D10) Change this formula, by adding $ signs as follows: =sum($C$10+$D$10) You will still get an answer of 20, but when you copy and paste the formula here it will copy the answer “20”. In this example, the $ sign always makes the formula add the two red cells together even when the formula is copied to any other part of the spreadsheet.

I will use these two formulas on the sales sheet so that I can transfer the total cost per item onto another sheet.

Other Formula

Coding

Coding is essential in my spreadsheet for receipts and ordering forms. To create my codes I will be using the concatenate formula. However this formula can be used to join together data contained in different cells, meaning it can take the first few letters, last few letters and also middle letters too. Therefore I will take the first few letters of the first word. I will then put them together by adding few numbers (this gives the identity of the item) and I will be putting these numbers together by the concatenate formula to give me the detail of the item, how many are in stock and what’s the price.