Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

group information in excel the limit no is constant

Posted on 2013-12-23
1
273 Views
Last Modified: 2014-01-08
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
Comment
Question by:controlit
1 Comment
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39736290
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question