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

Setting Blackberry "Desktop" contacts for display

Scenario: You connected the Blackberry phone to Outlook to transfer the contacts from BB to Outlook. On the phone, there are 250 contacts. But what is showing before synching is only 50. Where are the 200 staying from?

1. Check the BB phone if it has more than 1 email configured.
2. Next is to check the contacts from which it is displaying from, just verify if the contacts that are being displayed are coming from SIM, email1, email 2, or Desktop accounts, so you can identify the quantity of contacts and target each source differently.

From the scenario above, I have confirmed that the 50 contacts are coming from Gmail account, the other 200 contacts are from "Desktop" that I don't know what it is.
Anyway, I was able to figure out the solution to extract all 250 contacts from BB to Outlook. Pls follow the steps below:

1. Click Options
2. Device
3. Advanced System Settings
4. Default Services
5. Select Contact List (SYNC)
6. Select Desktop instead of the current selected, a Gmail account.
7. Save.
8. Now you're ready to sync it to Outlook.

Do the process of synching the BB to Outlook from this link.
Get the first 200 contacts, then go back to the default Gmail contacts and sync again into Outlook, by adding into the current Outlook contacts. You will be having the 250 contacts afterwards.

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