A Simple Computer Library

IF Application

To compare 2 conditions or more and also can be used to conduct conditional tests on values and formulas.
Please see examples below how if function is used.

This example using single if
To fill up cell C3 the formula is:
=IF(B3=’M',’MARRIED’, “NOT MARRIED’)
To fill up cell F3 the formula is:
=IF(F3>=60,’PASS’, “REMEDIAL’)
..
This example using multiple if

To fill up cell C3 the formula is:
=IF(B3=1,1000,IF(B3=2,2000,IF(B3=3,3000,4000)))
..
The next example is the combination of using IF with AND, OR

To fill up cell C3 the formula is:
=IF(AND(C3>=60,D3>10),”$ 10″,”$ 5″)
To fill up cell C3 the formula is:
=IF(OR(B3>=60,C3>10),”Pen”,”Book”)
Note:
AND (two conditions must be true)
OR (one condition true is still acceptable.

HLOOKUP and VLOOKUP

Practice 1

To fill up the Bonus in cell D2 and so on.
If we use Tabel 1, the formula is:
=HLOOKUP(C2,$G$2:$J$3,2)
If we use Tabel 2, the formula is:
=VLOOKUP(C2,$F$7:$G$10,2)

Practice 2

To fill up Name, the formula is:
=VLOOKUP(LEFT(A2,1),$A$8:$D$10,2)
To fill up Salary, the formula is:
=VLOOKUP(LEFT(A2,1),$A$8:$D$10,3)
To fill up Incentive, the formula is:
=VLOOKUP(LEFT(A2,1),$A$8:$D$10,4)
To fill up Bonus, if the two character code multiply by 100 is:
=RIGHT(A2,2)*100

Don’t forget to copy the formula to fill up the next row.
Note : Code have to sorted ascending in Table 1

Practice 3

To fill up JUICE NAME, the formula is:
=HLOOKUP(LEFT(B2,1),$B$13:$D$14,2)
To fill up SIZE, the formula is:
=MID(B2,2,3)
To fill up PACKAGE, the formula is:
=VLOOKUP(RIGHT(B2,1),$A$17:$C$19,2)
To fill up PRICE, the formula is:
=VLOOKUP(RIGHT(B2,1),$A$17:$C$19,3)

Protect and hide formulas

Select cell(s) whose formulas you want to hide using Format menu.
Click Cells and then click the Protection tab.
Click Hidden and click OK.
Click Tools menu, Protection, Protect Sheet.
Type password and click OK.
Reenter password to confirm, then click OK.
..
The step to remove protection:
Click Tools menu, Protection, Unprotect Sheet.
Type password and click OK.

LEN, UPPER, LOWER, PROPER, CONCATENATE

LEN function can be used to count the length of the string.
UPPER to make a text becomes uppercase.
LOWER to make a text becomes lowercase.
PROPER to make a text becomes properly right in writing.
CONCATENATE to join text(s) in cell.
& to join text(s) as well.
See the example picture below:

LEFT, MID and RIGHT

These functions are used to manipulate text in certain cell.
To make easier for you, see the picture below:

Copy and transpose data

Select certain cell(s) you want to transpose.
Click Copy button.
Select destination cell.
Click Edit, Paste Special, Transpose, OK.
Press Esc on your keyboard to end.

Copy specific cells

To copy specific cell of value content:
Select cell or certain cells to copy.
Right-click then choose Copy.
Select destination cell.
Right-click and choose Paste Special, Values then click OK.

Change image of a toolbar button

The step if you want to change toolbar button:
Right-click on the blank area on toolbar.
Click Customize.
Right-click the button on toolbar to change.
Click Change Button Image, then choose the new image you want.
Click Close from Customize dialog box.
..
To reset :
Right-click on the button image.
Click Customize.
Right-click on the button image.
Click Reset.
Click Close from Customize dialog box.

Freeze and Unfreeze Panes

Freeze Panes:
Select cell you want to freeze.
Click Window on the menu bar then click Freeze Panes.
Unfreeze Panes:
Click Window on the menu bar then click Unfreeze Panes.
..
The effect of freezing panes are:
The certain column that you select will not move, if you scroll to the right direction.
The certain row that you select will not move, if you scroll down.

Hide and unhide sheet

Hide sheet:
Click sheet name you want to hide.
Click Format, Sheet, Hide.
….
Unhide sheet:
Click Format, Sheet, Unhide.
Choose sheet name under Unhide sheet box.
Click OK.
….
Note:
If you only have one sheet only, you can’t hide.