ASSIGNMENT – 40
ANSWER KEY – SALESMAN PERFORMANCE ANALYSIS
Q1. How Many Salesmen Are There?
Formula
=COUNTA(A2:A12)Answer: 11 Salesmen
Q2. Target and Result of Ajeet
Target
=VLOOKUP("Ajeet",$A$2:$J$12,9,FALSE)Answer: ₹12,000
Result
=VLOOKUP("Ajeet",$A$2:$J$12,10,FALSE)Answer: ACHIEVED
Final Answer
Ajeet → Target ₹12,000 | ACHIEVED
Q3. Target and Result of Rahul, Pooja and Ashok
Using VLOOKUP
| Salesman | Target | Result |
|---|---|---|
| Rahul | ₹18,000 | NOT ACHIEVED |
| Pooja | ₹10,000 | ACHIEVED |
| Ashok | ₹10,000 | NOT ACHIEVED |
Target Formula
=VLOOKUP("Rahul",$A$2:$J$12,9,FALSE)Result Formula
=VLOOKUP("Rahul",$A$2:$J$12,10,FALSE)Repeat the same formula for Pooja and Ashok.
Q4. How Many Salesmen Achieved Their Target?
Formula
=COUNTIF(J2:J12,"ACHIEVED")Achieved Salesmen
-
Pooja
-
Manoj
-
Ajeet
-
Amrit
-
Surendra
Answer: 5 Salesmen
Q5. January Sales = 2,000 AND February Sales = 2,500
Formula
=COUNTIFS(B2:B12,2000,C2:C12,2500)Answer: 0 Salesmen
There is no salesman whose January Sales are exactly ₹2,000 and February Sales are exactly ₹2,500.
Note: Ramesh has January = ₹2,000, but February = ₹1,500.
Amrit has February = ₹2,500, but January = ₹1,800.
Q6. January Sales > 2,000 AND March Sales <= 1,500
Formula
=COUNTIFS(B2:B12,">2000",D2:D12,"<=1500")Matching Salesmen
| Salesman | January | March |
|---|---|---|
| Rakesh | 5,000 | 500 |
| Rahul | 3,000 | 1,200 |
Answer: 2 Salesmen
Q7. Total Sales Greater Than ₹12,000
Formula
=COUNTIF(H2:H12,">12000")Matching Salesmen
| Salesman | Total Sales |
|---|---|
| Rahul | ₹13,000 |
| Manoj | ₹14,900 |
| Ajeet | ₹14,300 |
| Amrit | ₹12,100 |
Answer: 4 Salesmen
Q8. Salesman Whose June Sales = ₹7,000
Using XLOOKUP
=XLOOKUP(7000,G2:G12,A2:A12)Answer: Ajeet
Ajeet’s June Sales = ₹7,000
Using INDEX + MATCH
=INDEX(A2:A12,MATCH(7000,G2:G12,0))Result: Ajeet
Q9. Conditional Formatting – Sales Between ₹2,000 and ₹5,000
Formula
=AND(B2>=2000,B2<=5000)Apply this rule to the January–June sales range.
Values that satisfy the condition include:
-
₹2,000
-
₹2,500
-
₹3,000
-
₹3,500
-
₹5,000
-
₹2,400
-
₹2,800
-
etc.
Formatting:
Font: Red
Background: Yellow
Q10. Conditional Formatting – Result
ACHIEVED
Formula:
=J2="ACHIEVED"Format: Green Fill
NOT ACHIEVED
Formula:
=J2="NOT ACHIEVED"Format: Red Fill
Result Summary
| Salesman | Result |
|---|---|
| Ramesh | 🔴 NOT ACHIEVED |
| Rakesh | 🔴 NOT ACHIEVED |
| Rahul | 🔴 NOT ACHIEVED |
| Pooja | 🟢 ACHIEVED |
| Manoj | 🟢 ACHIEVED |
| Ashok | 🔴 NOT ACHIEVED |
| Ajeet | 🟢 ACHIEVED |
| Alok | 🔴 NOT ACHIEVED |
| Amrit | 🟢 ACHIEVED |
| Surendra | 🟢 ACHIEVED |
| Shashi | 🔴 NOT ACHIEVED |
BONUS ANSWERS
Q11. Salesman with Highest Total Sales
Highest Sales:
₹14,900
Salesman: Manoj
Q12. Salesman with Lowest Total Sales
Lowest Sales:
₹7,600
Salesman: Ramesh
Q13. June Sales Greater Than ₹2,000
Formula
=COUNTIF(G2:G12,">2000")Matching Salesmen
-
Rakesh – ₹2,800
-
Rahul – ₹3,500
-
Ajeet – ₹7,000
-
Surendra – ₹3,000
Answer: 4 Salesmen
Q14. Three Conditions Using COUNTIFS
Conditions:
-
January Sales > ₹1,000
-
February Sales > ₹1,000
-
March Sales > ₹1,000
Formula
=COUNTIFS(B2:B12,">1000",C2:C12,">1000",D2:D12,">1000")Matching Salesmen
| Salesman | Jan | Feb | Mar |
|---|---|---|---|
| Rahul | 3,000 | 800 | 1,200 |
Rahul does not qualify because February is only ₹800.
The actual matching salesmen are:
| Salesman | Jan | Feb | Mar |
|---|---|---|---|
| Ajeet | 1,200 | 1,400 | 1,500 |
| Amrit | 1,800 | 2,500 | 1,700 |
| Shashi | 1,600 | 1,200 | 2,000 |
Answer: 3 Salesmen
Q15. Salesmen Whose Target is Exactly ₹12,000
The salesmen with Target = ₹12,000 are:
| Salesman | Target |
|---|---|
| Rakesh | ₹12,000 |
| Manoj | ₹12,000 |
| Ajeet | ₹12,000 |
| Amrit | ₹12,000 |
Answer: 4 Salesmen
COUNTIF Formula
=COUNTIF(I2:I12,12000)FINAL ANSWER SUMMARY
| Question | Answer |
|---|---|
| Q1 | 11 Salesmen |
| Q2 | Ajeet → ₹12,000, ACHIEVED |
| Q3 | Rahul → ₹18,000, NOT ACHIEVED; Pooja → ₹10,000, ACHIEVED; Ashok → ₹10,000, NOT ACHIEVED |
| Q4 | 5 Salesmen |
| Q5 | 0 Salesmen |
| Q6 | 2 Salesmen |
| Q7 | 4 Salesmen |
| Q8 | Ajeet |
| Q9 | Values between ₹2,000–₹5,000 → Red Font + Yellow Fill |
| Q10 | ACHIEVED → Green; NOT ACHIEVED → Red |
| Q11 | Manoj – ₹14,900 |
| Q12 | Ramesh – ₹7,600 |
| Q13 | 4 Salesmen |
| Q14 | 3 Salesmen |
| Q15 | 4 Salesmen |
Important Formulas
=COUNTA(A2:A12)=VLOOKUP("Ajeet",$A$2:$J$12,9,FALSE)=COUNTIF(J2:J12,"ACHIEVED")=COUNTIFS(B2:B12,">2000",D2:D12,"<=1500")=COUNTIF(H2:H12,">12000")=XLOOKUP(7000,G2:G12,A2:A12)