Well, upon looking at the three words Marcovian Queues, Arranged Marriage and Forefathers any sane person (Aah!, that excluded all the managers in the world :D) would say that they are totally unconnected. Today, i am here to prove that indeed they have a strong relationship among themselves. So much so that, the relationship is stronger than the bond between irrational decision making and adult managers.
(Warning: If you happen to be one of those rare readers with queer tastes and strong urge to scratch the head, i would suggest that you read it throughly, who knows, you may discover a new interest in GOD knows what. Others please continue reading)
* Scene 1 *
Not so long ago there is a person named Subrahmanya Uma Chenna Kesava Rao. All his friends used to call him SUCK-R or simply sucker. despite the fact that he doesnt know how to suck and he had never been to a b school in his entire life people persisted on calling him that. Nevertheless our topic today is neither his name nor his management style. As with most of the corrupted adults in a certain state in our country, our dear sucker also finished his engineering from a college named TITS (The Institute of Technology and Science). By dumbluck and mere fact that his name is longer than most of his classmates in the tits he got into a welknown software company called Huggies.
Several years sped past while our sucker was typing a cublicle for 12 hours a day 6 days a week 4 weeks a month and 13 months an year.
On a certain day in a certain year our dear sucker went to his home to visit his parents (names are not given due to constraints on maximum word limit of posts). By gods grace and bank balance they are still alive and kicking. After one day at home, the mom got this cruel idea. During the days dinner the family discussed about it. Sucker was initially reluctant to the whole damn thing. Later on the parents tried to convince him. Soon it became apparant to sucker that MARRIAGE is an unavoidable thing in life and everybody has to succumb one day or other. He told his parents to start looking for the girl.
* Scene 2 *
Not so long ago there is a person named Anuj (the name itself has got no connection what so ever with all the Anuj’s in this world :D). All his friends used to call him Anuj only. The anuj in the case also never been to a b school. But since our topic today is not whether a certain anuj has been to b school or not lets proceed with what we have to. Anuj was always fascinated by computers. He learned BASIC and LOGO during his schooling from a certain Public School. At the same time he got fascinated by the fairer sex in his class too. During the IX class he proposed to a certain Priya in his class. They were close to each other till class X when Priya had to move on with her life and say good bye to the certain A in her class.
the heartbroken anuj after spending several sleepless nights joined in a co-education college to continue the class 11 and 12 studies. after a brief break of 48 hours from the love life, anuj started fascinating about the college beauty ramya. This time around, since ramya being the college beauty and already having a couple of boy friends has to say NO to our dear anuj. After spending 18 worthless months trying to coax the R, our A started worrying about the graduation options. So he took a break from the adventures and concentrated on studies.
After 4 years and 8 girlfriends and dumbluck and rollnumber based selection, Anuj got into his dream company Huggies as a coder.
Armed with his new CBZ and debit card anuj started actively looking for the eligible females in his office space. during the first three months he moved with a certain Payal. Then he had to give up her becuase she is a money guzzler. Then he loved another girl. had to leave her because she doesnt know how to dance. then another one. left her because she is an engineer and lacks social skills. then another one.
Like this several years passed by…
* Scene 3 *
A queuing system has 2 essential componenets. The people who wants to get served. and the servers. For example is a railway reservation counter. The people buying tickets need the service and the counter serves them. Now the dynamics of queues itself requires a seperate blog and we wont be getting into them.
Now lets look at the entire process of love and selecting life partner as a queueing system.
All the people who will serve love (those who will be loved, all those college beauties, studs, chivalrous men, working women etc. etc.) are our servers. And as we all know these people with lovable qualities are very less.
All those people who will love (anybody and everybody. but for simplicity sake lets assume that those who are serving are not in this set) are in the queue waiting for the servers to get their thirst of passion quenched.
The following picture should give an idea.
Exhibit 1
Now queing theory tells me that the queue length before the servers in the exhibit will keep on growing. Also there is a possibility of a server crashing (here it means commiting with someone!) and thus creating hell lot of disappointment among the desperate waiters.
Inorder to better this situation people have devised a M/M/s system with a variation by pooling all servers together. This will reduce the wait time (here, the time one seeker needs to spend to find his life partner as well as the resources he spends in filtering down)
* Conclusion *
The arranged marriage system works like a pool. All those who are ready to serve love form a server pool (ie approach relatives and matrimonials and purohits :D). Now all those who want love will goto these pools and simply stand in queue. Since all the servers are pooled the waiting time is reduced. look at the following picture.
Exhibit 2
Now what is so great about this? Arent our forefathers wise enough to devise such system which reduces the burden of filtering down the right soulmate and saves hell lot of money/time/sleep/talk time.
* So what happened next? *
Soon after reaching his workplace in a matter of 2 weeks our sucker recieved a letter from his parents informing him that they have finalised his marriage with a certain Subba Lakshmi B Tech(Info.) (for a discerning reader the B. Tech is not part of her name). There are no limits to the happiness of sucker. <after sometime> they lived happily ever after.
Our dear anuj is still filtering. But this time he is using technology. webcams, sms chats, messengers, blind date websites. But tough luck. His waiting list number as of today is 549849859485948935.
THE END
24 Responses
I’d suggest simply using the subtotal function and filtering the data using the Win/Loss column. You get the same results and the formula is more comprehensible.
@John
That is one option.
There are times however when you want to see the whole data table or a filtered subset and still want to produce summary reports against an unfiltered field.
Is there a particular reason why you are using a comma and the unary (–) operator for the second array in the SUMPRODUCT formula? It seems to work the same if you were to string the arrays together using the asterisk (*). The advantage is that SUMPRODUCT treats the entire string of arrays as a single array.
@Mathew
Your correct, There is no difference.
I thought it may have been easier to explain this method.
Is there a way to do this on a large set of data? As in ~100,000 rows? When I try I get an error because the formula becomes too long. It says the max length of a formula is 8,192 characters. Excel 2010.
How do I incorporate a specific text within a cell for the second array. For instance, – -(C7:C13=”Apple”)
when I chose a specific text the formula does not work.
@RB
I am not sure what is the issue as if I use the sample data in the post the following work fine
Count:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)), –(C7:C13=”L”))
Sum:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C7:C13,ROW(C7:C13)-MIN(ROW(C7:C13)),,1)),(C7:C13=”L”)*(D7:D13))
You may want to check that there are no leading or trailing spaces in your list of Apples
I should have given a better explanation. Heres my situation. I have a column with cells filled with names like Column 1, Column 2, Pier 1, Pier 2, etc. If the cell just contained Pier and searched for that it works. But because it has other characters in the cell its not recognizing the pier. So how can I extract specific characters of a string of text in this formula?
Hopefully this was a better explanation
Hello-
This formula works pretty well for me except that it slow down excel and prevents some of my macros from working. I was wondering if there was a way to program this in VBA so that excel isn’t always trying to recalculate it. I would like to use a push of a button to get it to run then paste in a cell.
Thanks!
I am trying to sum filtered data in a column, but would want to ignore the negative values in the column. How to go about doing this?
@Akshay
Why not just add a filter to that column to only show the values greater than zero?
The negative values are required for reporting purposes, but their effect on the total is distorting the required output. Please advise.
@Akshay
I’d suggest making a post in the Chandoo.org Forums
http://forum.chandoo.org/
Attach a sample file to simplify the task
I have this working for counting and summing, however, I have a list and for the second array, I need a criteria. That is, I’m looking for b13:b200=”01.??.??” or =left((a1,2) or something like that. These types of criteria matches do not appear to work as I get a blank as a result.
Thanks!
@Bob
As your formula b13:b200=”01.??.??” looks like you are trying to check the first day of the month of the range
What about trying Day(B13:B200)=1
Hai Experts,
i understood this formula well and working fine in MS Excel 2013
but when the same am trying to place in google Spreadsheet it shows error as
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 2014, column count: 1.” and as a result #VALUE! Appears in cell.
Can anyone please help me how would i get it done in Google Spread sheet
or is there any other formula as a substitute for this.
Thank you very much.
thanks for providing this.. but why does excel keeps on prompting Circular referencing in cell D3?
@Vivek
I don’t know
I just downloaded the file and it is working fine and not showing that error
Goto the Formulas, Calculation Options Tab and check that Calculation is set to Automatic
What version of Excel and Windows are you using ?
I know that this forum is for MS Excel, but I am trying to help someone who is working in Google Sheets. The below formula works in Excel but Google Sheets returns:
“SUMPRODUCT has mismatched range sizes. Expected row count: 1. column count: 1. Actual row count: 39000, column count: 1.” and as a result #VALUE! Appears in cell.
This is the same problem asked by Srichirin above. Does anyone know if there is a formula for Google Sheets that will replicate what MS Excel does?
=SUMPRODUCT(SUBTOTAL(3,OFFSET($C$6:$C$39500,ROW($C$6:$C$39500)-MIN(ROW($C$6:$C$39500)),,1)),- -($C$6:$C$39500=H1),($D$6:$D$39500))
Trying to find a SUMPRODUCT formula that counts the word Closed by date for the last 7 days in a filtered list.
=COUNTIF(M:M,”>”&TODAY()-7) works ok for unfiltered count Column M contains Closure dates (blank if open) and Column L is Status Open or Closed
@ Terry
Please ask the question at the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to ensure a quicker more accurate answer
I used this formula and worked like a charm! But, now I’ve been requested to use it but adding not one but two criteria in the same formula. For instance the sum I was doing added negative and positive numbers. I’ve been asked to use the exact same formula but adding that only positive numbers were considered… any idea on how to do this?
How exactly do you do sum filtered cells when two criteria are need not just one?
Thank you so much brother literally I have been struggling since morning to get the sum of the filtered category, however, after reading your blog attentively i got my solution, so thanks a lot once again.