Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

group information in excel the limit no is constant

how i can to group information in excel user defined in dynamic table.
I have a pivot table with the source mysql database and I have a field called buffer
I need group by ranges only count so
                                                     count
between 0 and 33,9 -> green        59
between 34 and 66,9 -> yellow     4
between 67 and 100,9 red            45
greater than 101 gray                    20

are many records and the record is not constant
excel only you can group a range, but I have every range that is different
excel to have a origin of a database the records of PivotTable increase and decrease
is not CONSTANT

I can group rows in excel and then selecting the group and give the name to the group but the problem is that many records and change records. increase and decrement, so selecting  it has a limit in the selection.

THE MORE IMPORTANT
i upload file xls that contain a pivot table  with 56 values but that values can increase to 1500 or decrease to 700, no is Constant, so aply limits in for example =A4:A50 donĀ“t work for me beacuse in database can create a new value and pivot table change A4:A71,A4:A72,A4:A32,
please look the xls file in that file is very more details
agrupar-valores-buffer.xlsx
0
controlit
Asked:
controlit
1 Solution
 
NBVCCommented:
Create a Dynamic Named Range.

Go to Formulas tab, then Define Name.

Enter a name like MyList and add formula:

=OFFSET(Sheet2!$A$1,1,,COUNT(Sheet2!$A:$A),1)

Formulas in H4:H8:

=COUNTIFS(MyList,">101")
=COUNTIFS(MyList,">=67",MyList,"<=100.9")
=COUNTIFS(MyList,">=34",MyList,"<=66.9")
=COUNTIFS(MyList,">=0",MyList,"<=33.9")
=COUNTIFS(MyList,"<0")

You can also make the list in Sheet1 dynamic.  Say call it MyList2 and use similar formula:

=OFFSET(Sheet1!$D$1,,,COUNT(Sheet1!$D:$D),1)

Then you can adjust the PT source range to be =MyList2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now