ASSIGNMENT – 5 – ANSWER KEY

Sales Report

Completed Sales Table

Salesman JAN FEB MAR APR MAY JUNE SALES TARGET RESULT
AMAN 2,500 1,800 900 1,600 1,200 1,500 9,500 11,000 Not Achieved
VIVEK 4,200 1,500 700 1,300 1,400 2,600 11,700 12,000 Not Achieved
RAHUL 3,200 900 1,400 2,800 1,600 3,300 13,200 18,000 Not Achieved
PRIYA 1,200 1,100 1,900 450 1,500 1,300 7,450 11,000 Not Achieved
ROHIT 700 1,200 2,200 750 1,800 1,600 8,250 13,000 Not Achieved
ASHOK 900 700 2,500 2,100 1,900 2,000 10,100 10,000 Target Achieved
AJAY 1,400 1,600 1,700 800 2,700 6,500 14,700 12,000 Target Achieved
ALOK 1,700 1,900 2,000 1,700 500 1,600 9,400 10,000 Not Achieved
AMIT 1,900 2,600 1,800 1,600 2,900 1,900 12,700 12,500 Target Achieved
SURESH 300 2,800 2,000 1,400 1,600 2,900 11,000 10,000 Target Achieved

Q.1 COUNTA & VLOOKUP – AJAY

1. Total Number of Salesmen

Formula:

=COUNTA(A2:A11)

Answer: 10 Salesmen

2. AJAY’s Target

Formula:

=VLOOKUP("AJAY",$A$2:$J$11,9,FALSE)

Answer: ₹12,000

3. AJAY’s Result

Formula:

=VLOOKUP("AJAY",$A$2:$J$11,10,FALSE)

Answer: Target Achieved

Q.2 SUM – Total Sales

In H2:

=SUM(B2:G2)

Copy down to H11.

Answers

Salesman Total Sales
AMAN ₹9,500
VIVEK ₹11,700
RAHUL ₹13,200
PRIYA ₹7,450
ROHIT ₹8,250
ASHOK ₹10,100
AJAY ₹14,700
ALOK ₹9,400
AMIT ₹12,700
SURESH ₹11,000

Q.3 IF – Target Result

In J2:

=IF(H2>=I2,"Target Achieved","Not Achieved")

Copy down to J11.

Result

  • AMAN → Not Achieved

  • VIVEK → Not Achieved

  • RAHUL → Not Achieved

  • PRIYA → Not Achieved

  • ROHIT → Not Achieved

  • ASHOK → Target Achieved

  • AJAY → Target Achieved

  • ALOK → Not Achieved

  • AMIT → Target Achieved

  • SURESH → Target Achieved

Q.4 VLOOKUP – RAHUL

Target

=VLOOKUP("RAHUL",$A$2:$J$11,9,FALSE)

Answer: ₹18,000

Result

=VLOOKUP("RAHUL",$A$2:$J$11,10,FALSE)

Answer: Not Achieved

Q.5 VLOOKUP – PRIYA

Target

=VLOOKUP("PRIYA",$A$2:$J$11,9,FALSE)

Answer: ₹11,000

Result

=VLOOKUP("PRIYA",$A$2:$J$11,10,FALSE)

Answer: Not Achieved

Q.6 COUNTIF – Target Achieved

Formula:

=COUNTIF(J2:J11,"Target Achieved")

Answer: 4 Salesmen

They are:

  • ASHOK

  • AJAY

  • AMIT

  • SURESH

Q.7 COUNTIF – Not Achieved

Formula:

=COUNTIF(J2:J11,"Not Achieved")

Answer: 6 Salesmen

Q.8 LOOKUP – Find Salesman

We need to find the salesman whose:

  • January Sales = ₹2,500

  • February Sales = ₹1,800

The matching salesman is:

Answer: AMAN

For a two-condition lookup in modern Excel, one suitable formula is:

=XLOOKUP(1,(B2:B11=2500)*(C2:C11=1800),A2:A11)

Result: AMAN

If the assignment specifically requires the traditional LOOKUP function, this question can also be designed using a helper column. For teaching purposes, the XLOOKUP version is more reliable for two conditions.

Q.9 SUMIF – Total Sales of Achieved Salesmen

Formula:

=SUMIF(J2:J11,"Target Achieved",H2:H11)

Calculation:

  • ASHOK = ₹10,100

  • AJAY = ₹14,700

  • AMIT = ₹12,700

  • SURESH = ₹11,000

Answer: ₹48,500

Q.10 VLOOKUP – AMAN, ASHOK & AMIT

AMAN

Total Sales

=VLOOKUP("AMAN",$A$2:$J$11,8,FALSE)

₹9,500

Target

=VLOOKUP("AMAN",$A$2:$J$11,9,FALSE)

₹11,000

Result

=VLOOKUP("AMAN",$A$2:$J$11,10,FALSE)

Not Achieved

ASHOK

Total Sales

=VLOOKUP("ASHOK",$A$2:$J$11,8,FALSE)

₹10,100

Target

=VLOOKUP("ASHOK",$A$2:$J$11,9,FALSE)

₹10,000

Result

=VLOOKUP("ASHOK",$A$2:$J$11,10,FALSE)

Target Achieved

AMIT

Total Sales

=VLOOKUP("AMIT",$A$2:$J$11,8,FALSE)

₹12,700

Target

=VLOOKUP("AMIT",$A$2:$J$11,9,FALSE)

₹12,500

Result

=VLOOKUP("AMIT",$A$2:$J$11,10,FALSE)

Target Achieved

Final Answers

Question Answer
Q.1 Total Salesmen 10
Q.1 AJAY Target ₹12,000
Q.1 AJAY Result Target Achieved
Q.4 RAHUL Target ₹18,000
Q.4 RAHUL Result Not Achieved
Q.5 PRIYA Target ₹11,000
Q.5 PRIYA Result Not Achieved
Q.6 Target Achieved 4
Q.7 Not Achieved 6
Q.8 Salesman AMAN
Q.9 Total Achieved Sales ₹48,500

Q.10 Summary

Salesman Sales Target Result
AMAN ₹9,500 ₹11,000 Not Achieved
ASHOK ₹10,100 ₹10,000 Target Achieved
AMIT ₹12,700 ₹12,500 Target Achieved
PreviousNext