age band formula require..

[VBABEGINER]

Based on below data, I need formula to get age band..
Values are in Col E. Output require in Col F.

Col E

17
13
11
14
12
17
18
19
18
16
14
18
10
15
13
15

Age band require..
(10-13,14-16,17-20)...

 

[oldbrewer]

17
13
11						10	13	5	####
14						14	16	5
12						17	19	6
17						20	22	0
18						23	25	0
19						26	28	0
18						29	31	0
16						32	34	0
14
18
10
15
13
15						###
						=SUMPRODUCT(($A$1:$A$16>=G3)*($A$1:$A$16<=H3)*1)

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Marcílio_Lobão]

VBABEGINNER, Good afternoon.

There are a lot of ways to solve this kind of question using Excel.

Using VLOOKUP function:

Scenario:

Your data --> E2:E17

Create a little table as Support: (suggestion only)

	K	L	M
2	10	13	= K2 & "-" & L2
3	14	16	= K3 & "-" & L3
4	17	20	= K4 & "-" & L4

<tbody>
</tbody>

Try to use:

F2 -->

[B]=VLOOKUP(E2, $K$2:$M$4, 3, TRUE)[/B]

Copy the formulas down.

https://www.sendspace.com/file/qzg16h

Is that what you want?

I hope it helps.

 

[VBABEGINER]

Hi Oldbrewer, Thanks for reply. but i really dont understand solution. Thats fine.


Hello Marcílio,

Thanks for your reply. This works for me. Thanks for this trick.

VBABEGINNER, Good afternoon.

There are a lot of ways to solve this kind of question using Excel.

Using VLOOKUP function:

Scenario:

Your data --> E2:E17

Create a little table as Support: (suggestion only)

	K	L	M
2	10	13	= K2 & "-" & L2
3	14	16	= K3 & "-" & L3
4	17	20	= K4 & "-" & L4

<tbody>
</tbody>

Try to use:

F2 -->

[B]=VLOOKUP(E2, $K$2:$M$4, 3, TRUE)[/B]

Copy the formulas down.

https://www.sendspace.com/file/qzg16h

Is that what you want?

I hope it helps.

 

[oldbrewer]

read up on the sumproduct function - it is so powerful

 

[Marcílio_Lobão]

VBABEGINER, Good evening.

Glad to have helped you.

Thanks for the feedback.

Have a nice week!