Logical test is within range and is more than 1: Nested IF and SUMIF

I was challenge by my manager that he was able to do things at Excel that I amazes me it can be done,I've never known that it is possible. Though I got view of some logical tests way back in college and it could be implemented into Excel.
NESTED IF is used if you have multiple IF FUNCTIONS resulting to differing data. The syntax for NESTED IF is:

=IF( condition1, value if true1, IF( condition2, value if true2, value if false2 ))

Example:

If CR is manually inputted into a cell.
Slab is 0, if CR is equal to or below 49%.
Slab will result to 2.5, if CR is from 50-59%, and so on.

The syntax for above will become:

=IF(E4<=49%,0, IF(E4<=59%,2.5, IF(E4<=69%,3, IF(E4<=79%,3.5,4))))

_____________________________________________________________________________
Now let's go to SUMIF.
SUMIF is used to gather the sum of all items in the same group, provided you have more than 1 group.

Syntax is:

=SUMIF(range,criteria,sum of range)

where, range is like Acell:Acell
           criteria is anything
           sum of range is from Bcell:Bcell
Example:

On Columns A & B, all store with their staffs are listed.
On Column I, summary of stores (group) is listed with their total(sum).

Range = A4:A761
Criteria is pointed at Column I (where I listed all the store names)
Sumof range = G4:G761
For me to get the sum for each store, I have to use SUMIF, as you can see on the picture above @ fx.

To end: count then share blessings like Web traffic exchange to others

How to create a drop down list in Excel

Here's to how you can create a drop-down list in a cell or group of cells:

1. Create a list, every entry per cell on a column.
2. Select the cell to where to put the drop-down list
3. @ Data ribbon, goto Data Validation
    @ Settings Tab, @ Allow field, select List
    @ Source, select the created list in step#1
    OK

To end: count then share blessings like Web traffic exchange to others

How to calculate time in Excel

Checking into your subordinate's total spent time of work is beneficial for you in checking if they are misbehaving or not, or you would like to know when you have encountered overtime, then this will help you a lot. The calculations provided here is set to only one day, not between days.

BLACK ARROW:
1. Select the cell, right click,
    Format Cells
2. @ Normal Tab, select Custom
    Select h:mm AM/PM
    OK
RED OBLONG/BOX:
Select h:mm

CALCULATIONS:
 E3 = D3 - C3
 F3 = E3 - F1
 
To end: count then share blessings like Web traffic exchange to others

How to lock cells in Excel

There were times that we students don't want to show how we came up with calculations to our teachers or peers for fear of them using it to their advantage for submitting their sheets in lesser period of time, when you have done all the researching and they will simply benefit from it. Call it selfish but for me it's precaution, so they will do their part.

Here's how:
1. Select all the cells of the sheet.
    Right click any cell, Format Cells
    @ Protection Tab, uncheck Locked
    OK
2. Select all the column or select the group of cells to lock
    Right click, Format Cells
    @ Protection Tab, check Lock
    OK
3. @ ribbon, click on Review Tab
    Protect Sheet
    Tick all except Locked Cells
    Put password on top, reenter password, OK

To end: count then share blessings like Web traffic exchange to others