look up

[kumar1803]

I need help with putting my profit numbers in to the following range by using look Up Formula.

My Range that I would like to see are:

< 500
501-1000
1001-2500
2501-3500
above 3500

here is the real data that I like to put them in range by using Look up formula

Profit:

1000
50
-500
600
6000
4000
1200
800
700
500
450
345
234
1200
3400
5600

 

[alansidman]

What do you want your end result to look like with the examples provided.

 

[kumar1803]

because I have 10000 look up and I would like to look up a range of profits for how many items were sold with profit range <500, how many items sold with profit range 500-1000 and so on so forth.

 

[zaska]

A pivot table would solve the issue. Try inserting a Pivot table.

Group

<500

500-999

1000-1499

3000-3500

>3500

Grand Total

<tbody>
[TD="width: 48"]Profits[/TD]

[TD="align: right"]579[/TD]

[TD="align: right"]2600[/TD]

[TD="align: right"]3400[/TD]

[TD="align: right"]3400[/TD]

[TD="align: right"]15600[/TD]

[TD="align: right"]25579[/TD]

</tbody>

 

[Aladin Akyurek]

@kumar1803

What does "putting my profit numbers in to the following range" mean: counting, summing?

 

[kumar1803]

first I need make a range to insert a pivot table. Please provide a formula to make a range above

 

[kumar1803]

the formula should look like this

=LOOKUP(B3[500,1000,1500,2000,2500},{0,1000,1500,2000,2500}}

 

[zaska]

@kumar

No need to make a range. You can use the group option in pivot table.

1. Give Header to your profits column.
2. Insert Pivot table
3. Drag your Profits into Row Labels in pivot table field option
4. Right click the values in the pivot table and Group the profits to create Frequency distribution range.
5. Drag your Profits once again into values.

 

[kumar1803]

Hello All,

Please understand pivot table is not what I want. I know PT is an option but appreciate if you all can help me build this formula:

=LOOKUP(B3[500,1000,1500,2000,2500},{0,1000,1500,2000,2500}}

Thanks