Powerutils 1.9 is now available for download containing 99 utils.
If , And , Or Excel Functions for Decision making
A combination of these three functions can help you in putting complicated formulas in seconds to get highly qualified information from a excel spreadsheet database.
=IF(Condition)
The IF worksheet function checks a condition that must be either true or false, based on the
outcome different values are put for true condition and different for false condition.
Practise :To fill the below table
Salary Analysis
| Name | Basic | Less than 5000 | Equal to or greater than 5000 |
| Vipin | 4890 | ||
| Kaushal | 5000 | ||
| Krishna | 5234 | ||
| Udit | 6000 | ||
| Rahul | 4210 |
The If formula is written with the following arguments (parts)
"=if(condition,if true put this, else put this)"
In the Table above in the first column we want all the salaries below 5000 to come.
Put the formula =if(basic<5000,basic,0)
{Basic refers to the cell reference of Basic data for that Name}
In the Table above in the second column we want all the salaries 5000 and above
Put the formula =if(basic>=5000,basic,0) {Basic refers to the
cell reference of Basic data for that
Name}
Sample Working File download here
=Or(Condition) =And(Condition)
In a Decision making we often have multiple criteria based on which we take a positive or negative decision.
For example we have a database like this.
| Name | Basic | HRA given | Telephone | Poor | Rich |
| Vipin | 4890 | N | Y | ||
| Kaushal | 5000 | Y | N | ||
| Krishna | 5234 | Y | Y | ||
| Udit | 6000 | Y | Y | ||
| Rahul | 4210 | N | N |
We have to fill up the Column "Rich" with the following criteria using OR.
If Basic is greater than 5000 or HRA is Given or he has a Telephone then put YES or Else NO.
The formula arguments are as below.
=If(or(Basic>5000,HRA="y",Telephone="y"),"YES","NO")
We have to fill up the Column "Poor" with the following criteria using AND"
If Basic is less than 5000 and he doesn't get HRA and he doesn't have a Phone then put YES else put NO.
The formula arguments are as below.
=If(and(Basic<5000,HRA="y",Telephone="y"),"YES","NO")
The main difference between OR and AND is that in case of OR any one of criteria need to be matched to
make the decision YES but in case of AND all the criteria need to be satisfied to make the decision YES.
Sample Working File download here