• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding the maximum value in an array containing one or more rows

Hi Narayan,

Thanks for chiming in... I love it that you decided to join in. (Hopefully, there are no implicit rules in this forum that once somebody responded to a post, no others can join in, since I have breached that "rule" many times already!)


I agree with your suggestion, and will post a better data set and result set. (I just need to get to my regular computer since I read these posts on the weekend using a small device.)


Regards,

Sajan.
 
Hi Sajan,


Based on your original post, I understand what you are trying to do, as I tried something similar but with the PRODUCT function. Unfortunately, I could not figure out how to iterate through each row of the virtual array in a single formula (or named formula) without the use of helper columns (or a UDF).

I would be interested to see if someone could come up with a way to do so. Sorry I could not be of any help. As there has been a bit of confusion thus far, I just wanted to post to let you know that someone tried (with considerable effort) and did not succeed.


Kyle
 
Hi,

The following is some realistic data, showing the Items, Groups, and GroupSummary. Hopefully, this will make it easier to see how the source data needs to get transformed into the result set.


Items and monthly values:

[pre]
Code: 
Items	Jan-2012  Feb-2012	Mar-2012	Apr-2012	May-2012	Jun-2012
Item1	-	  15		10		12		12		1
Item2	67	  100		91		70		102		92
Item3	156	  222		90		6		15		9
Item4	-	  -		-		-		12		4
Item5	161	  223		176		158		206		107
Item6	-	  73		147		101		136		123
Item7	14	  20		16		14		18		12
Total	397	  652		529		360		501		347
GroupMembership and Effective Dates for the membership:

[pre][code]GroupMember	Membership	Effective Date
Item1		Group6		Jan-2011
Item2		Group3		Jan-2011
Item3		Group5		Jan-2011
Item3		Group4		Apr-2012
Item4		Group4		May-2012
Item5		Group4		Jan-2011
Item6		Group5		Feb-2012
Item7		Group7		Jan-2011
[/pre]
The effective date is the date when a group membership takes effect, overriding any previous group memberships. The group membership would be valid until overridden. For example, Item7 has been a member of Group7 since Jan 2011, and is in effect today since it has not been overridden. (In Excel, I have these dates actually set as the 1st day of each month. However, the usage is consistent everywhere, enabling straightforward comparison between two date values.)


The following is the desired Result set showing the summary by group by month. (This is the table I have been struggling with.)

Summary Jan-2012 Feb-2012 Mar-2012 Apr-2012 May-2012 Jun-2012
Group1 - - - - - -
Group2 - 12 5 - - -
Group3 67 100 91 70 102 92
Group4 161 223 176 164 233 120
Group5 156 295 237 101 136 123
Group6 - 15 10 12 12 1
Group7 14 20 16 14 18 12
Total 397 664 534 360 501 347[/code][/pre]
I have included a total row, to show that the totals from the first table will need to match the totals in the result set.

(The "-" shows zero values. I do not have text values in my data.)


Please let me know if I can clarify anything.


thanks,

Sajan.
 
Hi Sajan ,


Can you please check this file here ?


https://docs.google.com/open?id=0B0KMpuzr3MTVVUFtRjU2blc5aVE


I am not sure it does the job , so please verify.


I have used one helper column , and I have made two possibly unwarranted assumptions :


1. The items are named Item1 , Item2 , ...


2. The Items-Groups table is sorted on Groups.


Narayan
 
Narayan,


There is no problem to join to the discussion. Always two heads are better than one :)


Sajan,


I don't think so such a rule... The main concern is solve queries. Anyone can join to the discussion if they wish to.
 
Very interesting discussion.


Let me propose another solution. I think the way you structured your data is causing you all this head-ache. If you can restructure it like this -


http://img.chandoo.org/playground/restructure-data.png


then you can easily make a pivot and use group in row, month in column & value in values (with summarize by max) so that you can get desired result in one shot.


See this file: http://img.chandoo.org/playground/Copy%20of%20Sajan.xlsx


PS: I am using Narayan's file to add this new solution.
 
Hi Narayan,

Thank you for your feedback regarding the helper column. I appreciate your help.


(For some reason, I was not able to download the file you posted. (Could be firewall restrictions on my side.) However, I was able to get the file that Chandoo posted... Since he included your sheets also, that worked out!)


I was trying to see if I could get the summary results without having the helper date column, but perhaps it is not possible.

The reason I attempted to find a way without the helper column is because I was able to get the group for a single item for a single month using the following formula, but could not figure out a way to make it scale for the whole list of items:

=INDEX(ItemsList, SUMPRODUCT(MATCH(2, 1/((INDEX(GroupsList,0,1)=Item2Lookfor)*(INDEX(GroupsList,0,3)<=DesiredMonth)))),2)


(Having read Chandoo's survey regarding Excel age for folks, and realizing that I fall in the left end of that spectrum, I wanted to get feedback from some of the "Excel adults" before I ruled out that possibility!!)


Thanks for your help with this. I appreciate it very much.


Hi Chandoo,

Thanks for your suggestion. Unfortunately, in my specific situation, I am not able to change the layout of the source data. (Though it would have been desirable!)


Thanks again,

Sajan.
 
Sajan


I've been following this post from afar


I don't understand where Group 2

Code: 
Group2	-	  12		5		-		-		-


comes from ?


Can you please explain that
 
Hi Hui,

Good catch!! That was my mistake when creating a data set for this post. (My real-life data set has a lot more columns and rows, and refers to different entities like project role, team member, etc. I was attempting to generalize and simplify, while conveying the requirements.)


Those two data points (12 and 5) should be removed from the post.


thanks,

Sajan.
 
Hi Sajan, i know this is a very late response but the original problem of finding row-wise max values based on a rectangular array A is an interesting challenge and still appears unresolved (i think you mentioned it elsewhere which led me here.) Haseeb's response does the job when A refers to a sheet range. After a while thinking about this, here is what i came up with (utilising a couple of recently discovered tricks):

=MMULT(HLOOKUP(1,FREQUENCY(-ROW(A)-1,-ROW(A)-PERCENTRANK.EXC(A,A,20)),TREND(A*0+2*COUNT(A)+1,,,0)/3)*A,TRANSPOSE(INDEX(A,1,))*0+1)

[If no sheet references are allowed ROW(A) could be replaced by TREND(INDEX(A,,1)*0+2*ROWS(A)+1,,,0)/3].
 
actually for the data in question something like this might be sufficient:

=MOD(SMALL(1000*ROW(A)+A,COLUMNS(A)*ROW(A)),1000)

where data starts in row 1. For array data, ROW(A) could be changed to ROW(OFFSET(A:A,,,ROWS(A))) or a TREND formula similar to above. (This kind of approach should be ok for positive integer values but it would be less appropriate for decimal or fractional numbers like those generated using RAND() due to the limited precision.)
 
Hi Lori,
Thanks for revisiting an old problem. I had actually forgotten about it... Since I am a strong believer in inductive chain learning, a good one to revisit!

I have not had a lot of time to visit this forum recently... but I am looking forward to studying the above formula in the next few days!

-Sajan.
 
Interesting point of view, i suppose much of the experience built through everyday work could be thought of as inductive chain learning. The way i see it, reading reference material online or signing up for courses (deductive learning) is important but will only get you so far in programming and other technical disciplines. You also need to get your hands dirty, so to speak, by attempting problems that may not be solved using readily available techniques and instead apply knowledge gained from solving other problems (inductive learning).

But even more important i think is to come up with good questions - otherwise we wouldn't have the opportunity for such learning in the first place and sites like this one wouldn't exist!
 
You must log in or register to reply here.
Back
Top