Using VLOOKUP formula to find the total sum and using AVERAGEIF formula to find average sum

[prajwalsj]

Sheet 2 has information about the sales of various products for a manufacturer "XYZ" while sheet 3 has the information about the prices of these products. Using the data in Sheet 2 and Sheet 3 answer the following questions:
Q1: Using Vlookup formula, calculate the total money earned by "XYZ" during the 15 weeks for which we have the data available.
Q2: Using an averageif formula, get us the average sales volume of each product from the data.
Q3: If XYZ did a TV ad in week 4 which costed then 1.23 million Dollars, because of which they earned a total of 341 thousand Dollars, calculate the ROI(return on investment) for this ad campaign.

Interview Question.xlsx
	A	B	C
1	Product Code	Week	Sales Vol
2	1001	1	655
3	1002	1	999
4	1003	1	347
5	1004	1	596
6	1005	1	844
7	1006	1	487
8	1007	1	222
9	1008	1	570
10	1009	1	849
11	1010	1	905
12	1001	2	759
13	1002	2	989
14	1003	2	719
15	1004	2	541
16	1005	2	518
17	1006	2	232
18	1007	2	613
19	1008	2	824
20	1009	2	654
21	1010	2	429
22	1001	3	995
23	1002	3	150
24	1003	3	261
25	1004	3	279
26	1005	3	187
27	1006	3	576
28	1007	3	361
29	1008	3	858
30	1009	3	258
31	1010	3	723
Sheet2

Interview Question.xlsx
	C	D	E
1	Product Code	Week	Price
2	1001	1	10
3	1002	1	19
4	1003	1	25
5	1004	1	26
6	1005	1	31
7	1006	1	9
8	1007	1	12
9	1008	1	100
10	1009	1	71
11	1010	1	35
12	1001	2	11
13	1002	2	21
14	1003	2	23
15	1004	2	26
16	1005	2	31
17	1006	2	8
18	1007	2	15
19	1008	2	106
20	1009	2	70
21	1010	2	34
22	1001	3	10
23	1002	3	21
24	1003	3	25
25	1004	3	25
26	1005	3	30
27	1006	3	9
28	1007	3	14
29	1008	3	117
30	1009	3	71
31	1010	3	37
Sheet3

 

[jasonb75]

This looks very much like a homework assignment. You should really be asking your course tutor for guidance, not asking us to give you the answers.

If you show us what you have tried then people will help you through any mistakes or anything that you're not quite sure about but we're not going to do your whole assignment for you.

 

[prajwalsj]

This looks very much like a homework assignment. You should really be asking your course tutor for guidance, not asking us to give you the answers.

If you show us what you have tried then people will help you through any mistakes or anything that you're not quite sure about but we're not going to do your whole assignment for you.

Hey Jason, I have completed the assignment with a different approach. For question 1, I have multiplied sale volume with price to get the price for each product and calculated the sum to get the total money earned by the company xyz.
For question 2, I have calculated the average sales volume per product by using pivot table.
For question 3, I have calculated the Return of investment but i was wondering if the answer is correct.
The reason for posting this thread is to get the answer for question 1 and 2 using vlookup and averageif formula respectively. I will attach the answers below for the reference.

Interview Question.xlsx
	F	G	H	I
1	Total money earned	tv ad investment	return on investment	Total money earned
2	3682866	1200000	2482866	2490029.273
Sheet2

Cell Formulas
Range	Formula
F2,I2	F2	=SUM(E:E)
H2	H2	=F2-G2

Interview Question.xlsx
	G	H
14	Row Labels	Average of Sales Vol
15	1001	652.8666667
16	1002	697
17	1003	708.0666667
18	1004	563.6
19	1005	588.6
20	1006	501.0666667
21	1007	611
22	1008	835.5333333
23	1009	669.2666667
24	1010	685.0666667
25	(blank)
26	Grand Total	651.2066667
Sheet2

 

[jasonb75]

Realistically, whoever wrote the questions shouldn't be allowed to run an excel course.

For Q1. VLOOKUP is only useful for retrieving single pieces of information, a sum requires multiple pieces of information so VLOOKUP is the wrong thing to use, no competent excel user would even consider it. I might have a look at it later just for something to do but I wouldn't recommend it.

For Q2. =AVERAGEIFS(C:C,A:A,A2) would give you the average for product code 1001

For Q3. ROI is not something that I generally have to deal with, from what I can remember it is calculated as (Return - Cost)/Cost so from the example it would be (341000-1230000)/1230000 which equates to something like a 72% loss.
As I said though, not my area of expertise so that could be off the mark.

 

[prajwalsj]

Realistically, whoever wrote the questions shouldn't be allowed to run an excel course.

For Q1. VLOOKUP is only useful for retrieving single pieces of information, a sum requires multiple pieces of information so VLOOKUP is the wrong thing to use, no competent excel user would even consider it. I might have a look at it later just for something to do but I wouldn't recommend it.

For Q2. =AVERAGEIFS(C:C,A:A,A2) would give you the average for product code 1001

For Q3. ROI is not something that I generally have to deal with, from what I can remember it is calculated as (Return - Cost)/Cost so from the example it would be (341000-1230000)/1230000 which equates to something like a 72% loss.
As I said though, not my area of expertise so that could be off the mark.

Thanks Jason. You are absolutely correct. The questions are so confusing and lack details. I was doubting myself initially, but now I have got the clarity. These questions doesn't deserve to be posted on the platform like these. I will delete this thread soon.