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

CD/DVD drive is not detectable

If the CD drive is not listed from "My Computer", pls click this link first before doing the below steps.
There is a CD that can upgrade your current operating system to a new OS, aside from the usual genuine Windows CD that would cost more than the upgrade version. Unfortunately, there are times that when you upgrade it, suddenly the CD/DVD drive is not recognizable anymore. Going into registry of the computer is very critical coz 1 data entry change will make the computer non-working. Carefully follow the steps below.

I. DELETING UPPER FILTER
1. Click Start, type regedit at Run
2. In Registry Editor, expand My Computer, then expand HKEY_LOCAL_MACHINE
3. Expand SYSTEM, then CurrentControlSet
4. Expand Control, then Class
5. Click 4D36E965-E325-11CE-BFC1-08002BE10318
6. On the right, click UpperFilters
7. On the Edit Menu, click Delete

II. DELETING LOWER FILTER
1. Perform Steps 1-5 above
6. On the right pane, click LowerFilters
7. On the Edit Menu, click Delete

Exit Registry and restart the computer. This is mandatory for values to take effect.

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

How to transfer contacts and BBM contacts into Z10

RIM provides username and password to:

1. Download apps into your BB phone

2. Save your BBM contacts

3. other perks for using BB

Transferring the contacts from old BB to new Z10 is a piece of cake. Before Z10, all BB are using Blackberry Desktop Manager to transfer data from one BB to another. With Z10, you have to use a new software called Blackberry Link. Open it.

It is recommended to connect the old BB and the Z10 at the same time in doing the transfer.

1. There is called "Data Transfer" on the BB Link , click this and follow the prompt on the screen.  

2. Select the items that you would like to copy from old BB like contacts, calendar, sms, etc. Tick the boxes.

3. Then click on Transfer at the bottom right.

4. Be patient in using the software since it would take time to transfer the items, specially if you have loads of information from old phone, like 5 minutes or more.

For BBM contacts, use the username and password that you used in your old BB and sign into Z10 using the same. If you don't remember your BB userid and pwd, retrieve them using a link "Forgot password" and retrive the information using the email that you used in creating the BB userid. 

If BBM contacts can't be transferred, use manual invites to contacts.

There is another way of transferring the data using BB Protect. It is a cloud service to where you can save your phone information on the cloud prior to transferring them to Z10. Use the old BB phone in downloading this from AppsWorld and save whatever you needed saving, then on Z10, download the same and recover whatever you have saved.

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