Question:- Calculate following Salary Sheet.
|
ENO |
ENAME |
DESIG |
BASIC |
ALLOWANCE |
DEDUCTIONS |
NET PAY |
|
|
|
|
|
|
|
|
a. Configure DESIG column in such a way that is can’t store more than 4 characters. The probable values for DESIG are ADMN, MNGR, ACCT & PEON. (2)
b. Create formula for BASIC so that it is: (2)
- Rs. 7000 for PEON, Rs. 14000 for ACCT, Rs. 18000 for MNGR, Rs. 22000 for ADMN.
c. ALLOWANCE will be 15% of BASIC if he/she is not ADMIN or MNGR. (2)
d. DEDUCTIONS will be 1% of BASIC for PEON and 11% for MNGR and ACCT. For ADMN deduct 15% of BASIC. (2)
e. Create range names so that you can type = ALLOW – DEDU as formula for NET PAY column. (2)
Solution:-
Configure DESIGN column that can stores less than or equal to 4 characters.
Limit Characters Length in a Cell.
Step 1:- Select the range that you will limit date entries with specify character length.
Step 2:- Click the Data validation in the Data Tools group under Data tab.
Step 3:- In the Data Validation dialog box, select the Text Length item from the Allow: drop down box. See the following screen shot:
Step 4:- In the Data: drop down box, you will get a lot of choices and select one, see the following screen shot:
Step 4:- Entry exact number that you want to limit in Maximum/Minimum/Length box according to your needs and Click OK.
Now users can only enter text with limited number of characters in selected ranges.
Formula:-
Basic (D2):-
=IF(C2="PEON",7000,IF(C2="ACCT",14000,IF(C2="MNGR",18000,IF(C2="ADMN",22000))))
Allowance (E2): -
=IF(NOT(OR(C2="ADMN",C2="MNGR")),D2*15%,0)
Deduction (F2): -
=IF(C2="PEON",D2*1%,IF(OR(C2="MNGR",C2="ACCT"),D2*11%,IF(C2="ADMN",D2*15%)))
To create Range Names
Step 1:- Select the range for which you want to create a Named Range in Excel.
Step 2:- Go to Formulas –>Define Name.
Step 3:- In the New Name dialogue box, type the Name you wish to assign to the selected data range.
Do similarly for Deduction Column
Net Pay (G2):- =D2-ALLOW-DEDU
No comments:
Post a Comment