Pivot Table Grouping by custom value --- is it possible?

[PP3321]

I want to create following range.

Until 1000 it is grouped by 500.
After 1000, it is grouped by 1000.

1-500
500-1000
1000-2000
2000-3000
3000-4000
4000-5000
5000<

Do I need to use Macro to do this...?

 

[Khalid NGO]

Hi,

Not sure 100%.

See if the helper column works the way you want.

Regards,

 

[PP3321]

Hi Khalid,
I have not thought about helper column. Thank you for giving me new perspective!

Do you mean to use the formula like below, to see where the number falls?

=If(A1>1000,Floor(A1, 1000),Floor(Al,500))

 

[Khalid NGO]

Hi,

Yes, that could be an option, you will be ended up with more IFs more FLOORs...

Better to use 2 helper columns and make a lookup table.

Regards,

 

[PP3321]

yeah lookup table is much easier!!!! How come I could not think of that.
I ended up doing the long formula with nested ifs...
It was so much pain!!
I prefer to keep it simple to avoid errors.
Thank you!!!!

 

[PP3321]

I did not see your attachment!
wow thank you for this solution. This is so elegant...

=IFERROR(LOOKUP([@[Required Qty. ]],{1,500,1000,2000,3000,4000,5000},{"1-500","500-1000","1000-2000","2000-3000","3000-4000","4000-5000","5000>"}),"")

 

[Khalid NGO]

Hi,
Thanks for the feedback.

Little shorter with CHOOSE / MATCH:

=IFERROR(CHOOSE(MATCH(D2,{1,500,1000,2000,3000,4000,5000},1),"1-500","500-1000","1000-2000","2000-3000","3000-4000","4000-5000","5000>"),"")

or:

=CHOOSE(MATCH(D2,{0,1,500,1000,2000,3000,4000,5000},1),"","1-500","500-1000","1000-2000","2000-3000","3000-4000","4000-5000","5000>")

Regards,

 

[PP3321]

Wow it is so beautiful...thank you!!!!!!!!!!!!!!