What formula to be used

loknathmondal · May 17, 2018

Hi,

I have huge data which has lakhs of rows in excel

I want get the reward column to be filled automatically by using formula.

1. If a customer earns 225 points in any Quarter he will get Mango.
2. If a customer earns 450 points (225 points any qrt+ 225 points any qrt) in any 2 Quarter he will get Banana.
3. If a customer earns 700 points (225 points any qrt+ 225 points any qrt + 250 points any qrt) in any 3 Quarter he will get Apple.
4. If a customer earns 850 points (225 points any qrt+ 225 points any qrt + 225 points any qrt +175 points any qrt) in any 4 Quarter he will get Orange.

Data Given below.

SRNo	Customer Name	Qrt 1 - Point	Qrt 2 - Point	Qrt 3 - Point	Qrt 4 - Point	Reward
1	A. Edwin	1023	1988	1731	354
2	A.ISMAIL	539	741	624	246
3	A.Koteswararao	595	293	256	305
4	A.Nagaraju	1180	426	5959	5816
5	A.p Srivastav	647	448	1059	189
6	A K DAS	0	305	760	328
7	A Shankar	463	78	882	447
8	A sudhakar	988	309	696	28
9	A TRINATH REDDY	414	268	649	54
10	A.M.Babu	244	705	364	28
11	A.Madhu	576	262	260	27
12	A.RAMA RAO	579	491	460	127
13	A.SAIDULU	827	1048	673	3
14	A.Venkateswarao	136	254	378	434
15	AVISHEK .	0	1165	2721	380
16	AADHI NARAYAN	81	306	318	569
17	1.SK JALAL UDDIN 1.SK JALAL UDDIN	0	0	0	71
18	11.DHARADHAR SARDAR 11.DHARADHAR SARDAR	0	0	263	0
19	27.JIKIRIA SK 27.JIKIRIA SK	0	0	137	0
20	3.LAXMIRAM MURMU 3.LAXMIRAM MURMU	0	0	500	0
21	33.SIRAJUDDIN SK 33.SIRAJUDDIN SK	0	0	283	235
22	4.GHOSTHO GOPAL DAS 4.GHOSTHO GOPAL DAS	0	0	472	363
23	SUDHOY LOHAR	0	48	350	125
24	Shibu	54	21	28	0
25	A MALLESWARAN .	230	45	57	0
26	A MANI A MANI	0	0	722	145
27	A Manivel	0	0	54	0
28	A MIDYA	0	0	2	0
29	A MONDAL	0	1	0	0
30	A MONDAL	0	0	2	0
31	A MURTHY	0	34	93	0
32	ZASIMUDDIN SEKH	0	0	206	99
33	ZASIR AHEMAD .	0	61	209	7
34	ZATTAR	0	0	0	435
35	Zaved bhai	323	724	257	219
36	Zaveri Shah	0	0	1734	356
37	ZAWADUL	834	1173	2101	2682
38	ZAWED	310	328	232	0
39	ZAWED KHAN	0	0	156	366
40	ZAYED ALI	0	0	31	0

what formula i can use on this excel sheet
and there is lakhs of rows in the excel sheet.

Regards
Loknath

PCL · May 17, 2018

"1. If a customer earns 225 points in any Quarter he will get Mango"
=> it means at least 225 or exactly 225
the same for others

Cornejo · May 17, 2018

You could try this:

Code:

=IF(SUM(C3:F3)>=850,"Orange",IF(SUM(C3:F3)>=700,"Apple",IF(SUM(C3:F3)>=450,"Banana",IF(C3>=225,"Mango",IF(D3>=225,"Mango",IF(E3>=225,"Mango",IF(F3>=225,"Mango","")))))))

Manipulate the cell references as needed. Then drag or auto-fill to the end of your data.

jtakw · May 17, 2018

loknathmondal said:
1. If a customer earns 225 points in any Quarter he will get Mango.
2. If a customer earns 450 points (225 points any qrt+ 225 points any qrt) in any 2 Quarter he will get Banana.
3. If a customer earns 700 points (225 points any qrt+ 225 points any qrt + 250 points any qrt) in any 3 Quarter he will get Apple.
4. If a customer earns 850 points (225 points any qrt+ 225 points any qrt + 225 points any qrt +175 points any qrt) in any 4 Quarter he will get Orange.

Loknath

Hi,

I don't think OP is Just looking for the Total score for the rankings, this is what I believe OP wants according to his description:

Book1

A

B

C

D

E

F

G

1

SRNo

Customer Name

Qrt 1 - Point

Qrt 2 - Point

Qrt 3 - Point

Qrt 4 - Point

Reward

2

1

A. Edwin

1023

1988

1731

354

Orange

3

2

A.ISMAIL

539

741

624

246

Orange

4

3

A.Koteswararao

595

293

256

305

Orange

5

4

A.Nagaraju

1180

426

5959

5816

Orange

6

5

A.p Srivastav

647

448

1059

189

Orange

7

6

A K DAS

0

305

760

328

Apple

8

7

A Shankar

463

78

882

447

Apple

9

8

A sudhakar

988

309

696

28

Apple

10

9

A TRINATH REDDY

414

268

649

54

Apple

11

10

A.M.Babu

244

705

364

28

Apple

12

11

A.Madhu

576

262

260

27

Apple

13

12

A.RAMA RAO

579

491

460

127

Apple

14

13

A.SAIDULU

827

1048

673

3

Apple

15

14

A.Venkateswarao

136

254

378

434

Apple

16

15

AVISHEK .

0

1165

2721

380

Apple

17

16

AADHI NARAYAN

81

306

318

569

Apple

18

17

1.SK JALAL UDDIN 1.SK JALAL UDDIN

0

71

Not a Winner

19

18

11.DHARADHAR SARDAR 11.DHARADHAR SARDAR

0

263

0

Mango

20

19

27.JIKIRIA SK 27.JIKIRIA SK

0

137

0

Not a Winner

21

20

3.LAXMIRAM MURMU 3.LAXMIRAM MURMU

0

500

0

Mango

22

21

33.SIRAJUDDIN SK 33.SIRAJUDDIN SK

0

283

235

Banana

23

22

4.GHOSTHO GOPAL DAS 4.GHOSTHO GOPAL DAS

0

472

363

Banana

24

23

SUDHOY LOHAR

0

48

350

125

Mango

25

24

Shibu

54

21

28

0

Not a Winner

26

25

A MALLESWARAN .

230

45

57

0

Mango

27

26

A MANI A MANI

0

722

145

Mango

28

27

A Manivel

0

54

0

Not a Winner

29

28

A MIDYA

0

2

0

Not a Winner

30

29

A MONDAL

0

1

0

Not a Winner

31

30

A MONDAL

0

2

0

Not a Winner

32

31

A MURTHY

0

34

93

0

Not a Winner

33

32

ZASIMUDDIN SEKH

0

206

99

Not a Winner

34

33

ZASIR AHEMAD .

0

61

209

7

Not a Winner

35

34

ZATTAR

0

435

Mango

36

35

Zaved bhai

323

724

257

219

Orange

37

36

Zaveri Shah

0

1734

356

Banana

38

37

ZAWADUL

834

1173

2101

2682

Orange

39

38

ZAWED

310

328

232

0

Apple

40

39

ZAWED KHAN

0

156

366

Mango

41

40

ZAYED ALI

0

31

0

Not a Winner

Sheet57

Cell Formulas
Range	Formula
G2	=CHOOSE(IF(AND(COUNTIF(C2:F2,">=225")>=3,MIN(C2:F2)>=175),1,IF(AND(COUNTIF(C2:F2,">=225")>=3,COUNTIF(C2:F2,">=250")>=1),2,IF(COUNTIF(C2:F2,">=225")>=2,3,IF(COUNTIF(C2:F2,">=225")>=1,4,5)))),"Orange","Apple","Banana","Mango","Not a Winner")

G2 formula copied down.

Cornejo · May 17, 2018

Ahhhhh, I should've read more into that. Great solution.

loknathmondal · May 18, 2018

jtakw said:
Hi,

I don't think OP is Just looking for the Total score for the rankings, this is what I believe OP wants according to his description:

A B C D E F G
1 SRNo Customer Name Qrt 1 - Point Qrt 2 - Point Qrt 3 - Point Qrt 4 - Point Reward
2 1 A. Edwin 1023 1988 1731 354 Orange
3 2 A.ISMAIL 539 741 624 246 Orange
4 3 A.Koteswararao 595 293 256 305 Orange
5 4 A.Nagaraju 1180 426 5959 5816 Orange
6 5 A.p Srivastav 647 448 1059 189 Orange
7 6 A K DAS 0 305 760 328 Apple
8 7 A Shankar 463 78 882 447 Apple
9 8 A sudhakar 988 309 696 28 Apple
10 9 A TRINATH REDDY 414 268 649 54 Apple
11 10 A.M.Babu 244 705 364 28 Apple
12 11 A.Madhu 576 262 260 27 Apple
13 12 A.RAMA RAO 579 491 460 127 Apple
14 13 A.SAIDULU 827 1048 673 3 Apple
15 14 A.Venkateswarao 136 254 378 434 Apple
16 15 AVISHEK . 0 1165 2721 380 Apple
17 16 AADHI NARAYAN 81 306 318 569 Apple
18 17 1.SK JALAL UDDIN 1.SK JALAL UDDIN 0 0 0 71 Not a Winner
19 18 11.DHARADHAR SARDAR 11.DHARADHAR SARDAR 0 0 263 0 Mango
20 19 27.JIKIRIA SK 27.JIKIRIA SK 0 0 137 0 Not a Winner
21 20 3.LAXMIRAM MURMU 3.LAXMIRAM MURMU 0 0 500 0 Mango
22 21 33.SIRAJUDDIN SK 33.SIRAJUDDIN SK 0 0 283 235 Banana
23 22 4.GHOSTHO GOPAL DAS 4.GHOSTHO GOPAL DAS 0 0 472 363 Banana
24 23 SUDHOY LOHAR 0 48 350 125 Mango
25 24 Shibu 54 21 28 0 Not a Winner
26 25 A MALLESWARAN . 230 45 57 0 Mango
27 26 A MANI A MANI 0 0 722 145 Mango
28 27 A Manivel 0 0 54 0 Not a Winner
29 28 A MIDYA 0 0 2 0 Not a Winner
30 29 A MONDAL 0 1 0 0 Not a Winner
31 30 A MONDAL 0 0 2 0 Not a Winner
32 31 A MURTHY 0 34 93 0 Not a Winner
33 32 ZASIMUDDIN SEKH 0 0 206 99 Not a Winner
34 33 ZASIR AHEMAD . 0 61 209 7 Not a Winner
35 34 ZATTAR 0 0 0 435 Mango
36 35 Zaved bhai 323 724 257 219 Orange
37 36 Zaveri Shah 0 0 1734 356 Banana
38 37 ZAWADUL 834 1173 2101 2682 Orange
39 38 ZAWED 310 328 232 0 Apple
40 39 ZAWED KHAN 0 0 156 366 Mango
41 40 ZAYED ALI 0 0 31 0 Not a Winner

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet57

Worksheet Formulas

Cell Formula
G2 =CHOOSE(IF(AND(COUNTIF(C2:F2,">=225")>=3,MIN(C2:F2)>=175),1,IF(AND(COUNTIF(C2:F2,">=225")>=3,COUNTIF(C2:F2,">=250")>=1),2,IF(COUNTIF(C2:F2,">=225")>=2,3,IF(COUNTIF(C2:F2,">=225")>=1,4,5)))),"Orange","Apple","Banana","Mango","Not a Winner")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

G2 formula copied down.

Thanks a lot.

Really appreciated.

Regards
Loknath

jtakw · May 18, 2018

You're welcome.

What formula to be used

loknathmondal

New Member

Excel Facts

PCL

Well-known Member

Cornejo

New Member

jtakw

Well-known Member

Cornejo

New Member

loknathmondal

New Member

jtakw

Well-known Member

Forum statistics

Share this page

What formula to be used

loknathmondal

New Member

Excel Facts

PCL

Well-known Member

Cornejo

New Member

jtakw

Well-known Member

Cornejo

New Member

loknathmondal

New Member

jtakw

Well-known Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock