Question

Company ID	1	2	3	4	5	6	7	8	9	10	11	12	13	14
1	5330	6352	6340	3399	6566	7376	6882	1842	7362	8205	6222	7695	1681	2283
2	7972	5941	2861	3290	1019	7399	6442	8003	5092	4634	7569	2538	2619	7932
3	8545	6667	2121	2339	4770	7296	3215	7919	2176	9129	1321	695	7945	1673
4	4578	466	4142	8350	7439	6085	8381	5872	2703	6974	702	5846	7176	9179
5	7302	2727	3103	7626	1587	152	1703	7555	7933	2909	6907	6706	3230	5961
6	1897	1004	2010	5347	7317	7296	4274	9668	933	464	5940	1800	7241	3500
7	9673	3027	8230	5254	2387	2204	3757	2198	4277	3363	1421	6232	7141	1058
8	5087	3357	4366	8394	1112	5339	7816	40	2661	2344	4169	9030	3342	6448
9	5231	2129	8041	3736	84	7751	7037	757	4196	6542	2284	8692	2194	9069
10	932	8292	6741	4472	8125	8098	1376	4459	609	4390	3383	7026	3724	1077
11	9339	1636	4046	8853	6472	3421	8477	421	5982	7183	937	6464	9546	120
12	2578	668	2880	1070	5176	2053	5537	7207	9743	5352	6070	3025	559	7095
13	7733	62	6281	6440	4685	5765	6021	6489	9301	7345	7595	8682	4337	6967
14	5129	7523	531	8214	7844	1324	1874	5539	2852	1405	1357	8172	3777	9676
15	3302	2895	3723	4878	8164	7557	622	8190	8919	104	9372	2238	7434	7909
16	8186	1823	9237	6659	5545	2644	6701	627	4053	5154	1397	9701	6229	6093
17	1289	7197	9180	6347	1669	8184	7139	3079	7598	264	1063	8523	390	1332
18	8992	1298	1372	2314	9826	9583	4291	4432	9447	7985	1622	9888	8798	1696
19	114	7637	9595	5612	9926	9296	8708	3297	8470	3881	2893	5397	9705	3073
20	2900	1997	7434	3348	909	593	5041	8253	7435	8234	4179	168	1196	4171
21	2484	7699	8177	7154	8270	9399	5589	4150	1434	559	1802	5397	9389	337
22	8956	8731	4600	190	3601	2678	9160	9896	6589	6981	8874	6861	3812	2602
23	5620	2361	9121	4453	5495	8411	6226	1594	2460	7285	8325	1706	8111	9610
24	2006	333	2785	3094	3338	4394	963	144	8169	3601	7283	8759	9617	4907
25	6763	8713	7986	5532	3823	7845	1235	3588	7295	7026	6889	838	5453	4245

Question 1	Identify the largest net income number						(Use Conditional Formatting for this question)
	For that cell, calculate: "Company ID"*100 + quarter
	What is the value you get?
	For example, if the largest net income belongs to "company ID": 3 and in quarter 5
	the number for your answer should be: 3*100 + 5 =305
Question 2	Identify the 3 smallest net income numbers.						(Use Conditional Formatting for this question)
	What is the sum these 3 numbers?
Question 3	How many cells have net income more than 5000?
Question 4	How many cells have net income less than or equal to 4600?
Question 5	If net income in a given cell (firm/quarter) is less than 2000 OR more than 6000, give grade 1 for that cell
	if not, give grade 3
	Find the total of the grade for all cells.
Question 6	If net income in a given cell (firm/quarter) is more than 3000 AND less than 5000, give grade 1 for that cell.
	If not, give grade 5
	Find the total of the grade for all cell.
Question 7	Analyze the 1st quarter only.
	Create a new column call "level1".
	In this colum:
	If net income of a company is less than 3000, then give value 2
	If net income of a company is more than or equal to 3000, then give value 3
	What is the total of the "level1" column?
Question 8	Analyze the 1st quarter only.
	Find the average net income for that quarter.
	Create a new column call "level2".
	In this colum:
	If net income of a company is less than the above average, then give value 2
	If net income of a company is more than or equal to the above average, then give value 3
	What is the total of the "level2" column?
Question 9	If net income in a given cell (firm/quarter) is more than 2000 and less than 4000, give grade 1 for that cell.
	If not, give grade 5
	Sum the grade up and report the sum.
Question 10	If net income in a given cell (firm/quarter) is less than 2500 OR more than 4500, give grade 1 for that cell
	if not, give grade 3
	Sum the grade up and report the sum.
Question 11	For each company, calculate the quarterly average for Net Income.
	(so each company has 01 number of average, right)
	For a give company/quarter (cell), if net income is greater than the above average, give value 3
	if not, give value 1.
	So you now you have a big table with value 1 or 3. Now sum these number up and what is the value of that sum?
Question 12	For each quarter calculate the average for Net Income across these companies
	(so each quarter has 01 number of average, right)
	For a give company/quarter (cell), if net income is greater than the above average, give value 3
	if not, give value 1.
	So you now you have a big table with value 1 or 3. Now sum these number up and what is the value of that sum?

Answer 1

Using Conditional formatting

Answer to the Question 1 :

Largest No belogs to Company ID : 19 & No. is 9926 , Quarter is 5

Therefore , final answer is ( As per formula given in question ) = (19*100) + 5

= 1905

Answer to the Question 2 :

Three Smallest Nos. are : 40 , 62 & 84

Sum of these nos. is : (40+62+84) = 186

Answer to the Question 3:

Total no. of cells are having net income more than 5000 = 184

Answer to the Question 4 :

Total no. of cells are having net income less than or equal to 4600 = 161

Answer to the Question 5 :

Total no. of cells are having net income less than 2000 or more than 6000 ( Grade 1) = 226

Total no. of cells are not having net income less than 2000 or more than 6000 ( Grade 3) = 124

Answer to the Question 6 :

Total no. of cells are having net income more than 3000 AND less than 5000 ( Grade 1) = 58

Total no. of cells are not having net income more than 3000 AND less than 5000 ( Grade 5) = 292

Answer to the Question 7 :

The total no. of Companies having Net income less than 3000 ("Value 2") = 8

The total no. of Companies having Net income more than or equal to 3000 ("Value 3") = 17

Answer to the Question 8 :

Average net income for the quarter 1 = 5277.52

The total no. of Companies having Net income less than 5277.52 ("Value 2") = 13

The total no. of  Companies having Net income more than or equal to 5277.52 ("Value 3") = 12

Answer to the Question 9 :

The total no. of Companies having Net income more than 2000 and less than 4000 ("Grade 1") = 67

The total no. of Companies are not having Net income more than 2000 and less than 4000 ("Grade 5") = 283

Answer to the Question 10 :

The total no. of Companies are having Net income less than 2500 or more than 4500 ("Grade 1") = 282

The total no. of Companies are not having Net income less than 2500 or more than 4500 ("Grade 3") = 68

Answer to the Question 11 :

The total no. of companies are having Net income greater than average (Value 3) = 180

The total no. of companies are not having Net income greater than average (Value 1) = 170

Answer to the Question 12 :

The total no. of companies are having Net income greater than quarter average (Value 3) = 177

The total no. of companies are not having Net income greater than above average (Value 1) = 173