Use Excel Formulas to Make Comparisons or Join Text
In addition to using Excel formulas to perform mathematical calculations, by using a comparison operator, you can make comparisons. Comparison operators compare two values. For example, the following formula: =B1>A1, checks to see if the value in cell B1 is greater than the value in cell A1. The > sign is the comparison operator. Comparison operators return FALSE if the comparison is false, in the case of our example, if B1 is not greater than A1 and TRUE if the comparison is true, in the case of our example, if B1 is greater than A1. I explain all the comparison operators in the Comparison Operators table.
| Comparison Operators | ||||
|---|---|---|---|---|
| Operator | Name | Description | Example | Result |
| = | Equal | Determines if one value is equal to another value | =1=1 | TRUE |
| <> | Not equal | Determines if one value is not equal to another value | =1<>1 | FALSE |
| > | Greater than | Determines if one value is greater than another value | =1>2 | FALSE |
| < | Less than | Determines if one value is less than another value | =1<2 | TRUE |
| >= | Greater than equal to | Determines if one value is greater than or equal to another value | =1>=2 | FALSE |
| <= | Less than equal to | Determines if one value is less than or equal to another value | =3<=2 | FALSE |
The values TRUE and FALSE are logical values. Logical values are values that can only be one of two options.
In calculations, TRUE is equal to1 and FALSE is equal to 0. The formula =1+TRUE is equal to 2.
Joining Text
The process of joining text is called concatenation. The & is the concatenation operator. If cell A1 contains the value Widget and you type the formula =A1& " Sales" in another cell, the result will be Widget Sales. When including text in a formula, enclose the text in double quotes.
Working out a spreadsheet to derive incentive payment for worker.
Data of worker depending on performances over day, month and year.
Final year score will determine the incentive payment
100% gets $XXX, 75-99% gets $YYY, 50-75% gets $ZZZ, < 50% get $AAA
Which formula could be appropriate. ?
Thanks
I apologize for taking so long to get back to you. I did not notice your comment yesterday. The short answer is use the following formula, where cell B7 is where you store the incentive payment percent and 1000 is the amount you get for 100 percent, 800 is the amount you get for 99 to 75 percent, 600 is the amount you get for 74 to 50 percent and 400 is the amount you get for anything less than 50 percent.
=IF(B7=100,1000,IF(AND(B7<100,B7>74)=TRUE,800,IF(AND(B7<75,B7>49)=TRUE,600,IF(AND(B7<50, B7>-1),400,”Invalid Entry”))))
It is a complicated formula. Click here for a detailed explanation.