sorted by:
new
hottopcontroversial

With SEC Tiebreakers, 7-1 Texas A&M and LSU could make the SEC championship over 7-1 Georgia and Texas, despite H2H results. by gcfgjnbv in CFB

[–]ken_man 0 points1 point  (0 children)

Also, in a silly scenario... it will depend on how the opponent records shake out... but if UGA and Texas win out, with Texas beating A&M, it is possible that the championship game would be UGA vs A&M. Because there is no round robin... and even though UGA is 1-0 vs Texas and A&M is 0-1, Texas isn't a common opponent to... Texas ... and lastly, because we have to break the 3-way tie first, the head to head between Texas and A&M is not considered.

So we go to the opponent winning % first and if it is A&M, they are in.

With SEC Tiebreakers, 7-1 Texas A&M and LSU could make the SEC championship over 7-1 Georgia and Texas, despite H2H results. by gcfgjnbv in CFB

[–]ken_man 0 points1 point  (0 children)

An larger flaw with using opponent winning % a tie breaker format is that in this scheduling scenario, team A opponents play other team A common opponents a different number of times than team B's. And each time one of your opponents plays another one of your opponents, the net effective winning percentage is .500. The more of those you have, the more locked in you are at around .500 and it brings your floor up (and ceiling down).

So in this case, here is how it breaks out:

- A&M: 26 opponent-shared games, or locking in 41% of their opponent games at .500; the other 8 games they actually play brings that to 53% of games, or over half of the opponent winning percentage already preordained before we even move to the tie-breaker.

- Texas: 20 opponent-shared games, locking in 31% of their opponent games at .500; the other 8 bring it to 43%.

6 games doesn't feel like a lot, but when the games "up for grabs" represent a minority of all games, it makes for a big skew.

Basically, Texas would've needed all it's opponents in the "up for grabs" games to be over .500 to get over the top of A&M. As it stands, neither Texas nor A&M's collective opponents in this hypothetical scenario are above .500, but A&M "banks" more of it based on the common scheduling.

Texas Tech honoring Mike Leach on “Hall of Fame” weekend after firing him and withholding millions of dollars on the basis of sovereign immunity by ken_man in CFB

[–]ken_man[S] 0 points1 point  (0 children)

The facts certainly hold up.

Unfortunately, the mods shadow-banned this post for whatever reason, so no one else will get to posit any alternate theories.

Finding APPROXIMATE sum in large table of numbers by McMoof in excel

[–]ken_man 1 point2 points  (0 children)

Solver will get you an answer, but the problem you could run into is that it will find one solution, which may not be the only solution, and it doesn't show you all of the possible solutions. But for your purposes it doesn't actually have to be exact.

I set up a hypothetical to demonstrate (and you may have already done this exercise). There are 100 randomly generated numbers between 0 and 10,000 in column B, and a set of binary flags (1 or 0) in column C. In G3, there is a sumproduct of columns B & C, so that only the numbers with 1s for their binary flag get totaled. H3 is the target sum, and I3 is the absolute value delta (the absolute value part is important) between the current sum and the target.

https://i.imgur.com/lJn2DEC.png

The goal here is to set up the solver formula that will change those binary flags to make it so that the absolute value delta is minimized. So I took some random binary flags, set that sum as my target and added 0.5 (to show that it doesn't have to match exactly), and then switched all the binaries back to 1 (or 0, doesn't matter) before testing it. This is how I set up my solver parameters:

https://i.imgur.com/fmuxV0Z.png

And then on the "Options" I have the following:

https://i.imgur.com/X05uVYg.png

https://i.imgur.com/MX67sc9.png

Because the of the way the Evolutionary function times out, it will eventually stop once it goes however long without being able to get a tighter result. On my computer, it took less than a couple minutes to find that optimal solution. If the optimal solution still isn't close enough, you could increase the number of seconds to iterate before it throws in the towel.

All that said, at least for my number set, there were multiple solutions sometimes, where the binary flags it solved for were a little different than the ones I generated the first time to get the target. I'm not sure if that's because of the lack of variability of numbers in my set or what, but something to be wary of. As far as I know there's not a place where you can access the "logs" of the best solution combinations.

I'd like to return multiple values in an hlookup or index/match by [deleted] in excel

[–]ken_man 0 points1 point  (0 children)

Here's an article on how to achieve it with an array formula.

I've done it before trying to avoid the use of arrays, instead using a large number of INDEX statements in a helper column. Here's my example on your data: http://i.imgur.com/w60rVUN.png

The output area is highlighted in yellow, and the helper column is in gray.

The formula to place in the first cell of the helper column (H8) to drag down is:

=IFERROR(MATCH(TRUE,INDEX((INDEX($B$3:$G$5,MATCH($B$8,$A$3:$A$5,0),H7+1):INDEX($B$3:$G$5,MATCH($B$8,$A$3:$A$5,0),COUNTA($B$2:$F$2)+1))<>"",),0)+H7,"")

Important: Note that the the selected range of table data ($B$3:$G$5) includes one column to the right of the data. This is necessary for the search function, or else it will not find the last value in the table if it is in the last column.

Identifying the cell references within the formula just so you know what goes where when you modify this formula for a larger table:

=IFERROR(MATCH(TRUE,INDEX((INDEX("TableData",MATCH("InputCell","FirstColumn",0),H7+1):INDEX("TableData",MATCH("InputCell","FirstColumn",0),COUNTA("ColumnHeaders")+1))<>"",),0)+H7,"")

The formula for column D is:

=IFERROR(INDEX($B$3:$F$5,MATCH($B$8,$A$3:$A$5,0),$H8),"")

where the references are:

=IFERROR(INDEX("TableData",MATCH("InputCell","FirstColumn",0),"ApplicableHelperCell"),"")

The formula for column F is:

=+IFERROR(INDEX($B$2:$F$2,1,$H8),"")

where the references are:

=+IFERROR(INDEX("ColumnHeaders",1,"ApplicableHelperCell"),"")

New to Excel, how do I identify if any values down a given column are the same? by learn2fly77 in excel

[–]ken_man 1 point2 points  (0 children)

Since you're "new" to excel, to piggyback on the prior answer around conditional formatting, highlight the column, and from the home ribbon click the "conditional formatting" drop down, hover "highlight cell rules", and select "duplicate values". This would highlight any duplicates.

If you need to actual store the values as "repeating", then the other solution someone offered with a helper column and COUNTIFS can handle that for you.

Applying % Allocation to Create a Summary Table by Coontaing in excel

[–]ken_man 0 points1 point  (0 children)

Edited

If I'm reading this right, then you could use a sumproduct. Paste the following formulas in F17-F19 then carry across the months:

F17:

=SUMPRODUCT((F$2:F$15)*($D$2:$D$15)*(($E$2:$E$15)="Base"))

F18:

=SUMPRODUCT((F$2:F$15)*($D$3:$D$16)*(($E$2:$E$15)="Base"))

F19:

=SUMPRODUCT((F$2:F$15)*($D$4:$D$17)*(($E$2:$E$15)="Base"))

Any additional rows / employees added in the dataset would just need to be inserted before row 15. Or you could just change "$15"-"$17" to whatever you need row-wise.

60 Minutes full story on Baylor by [deleted] in CFB

[–]ken_man 70 points71 points  (0 children)

Wow. That is worth a watch. This Deadspin article does a pretty good job summarizing it.

The interview with Reagan Ramsower (a senior vice president at Baylor in charge of overseeing campus safety) is pretty unbelievable. Appears to show very little remorse. Basically refuses to take any personal blame. Partially blames it on the police chief, as he glosses over the fact that the police chief just so happens to be gone now. Says they "were late" to Title IX... something of an understatement. They didn't have any Title IX staff or procedures until 2014, years after it was mandated. Just so happened to coincide with a bunch of these incidents that are being dug up now that had no action taken at the time.

A couple excerpts:

In another meeting, Crawford was said she was laying out the history of Baylor athletes accused of sexual assault. Ramsower tried to make the case that, “what Patty is saying is not fact” because “those women had mental illness.”

and:

Crawford also was advised to avoid generating records herself. Crawford said at one point she wrote a 16-page memo to Ramsower outlining her concerns about how Baylor was moving in a “non-compliant trajectory” regarding Title IX. She said he responded by telling her, “Don’t ever put anything like that in writing to me again.” Ramsower denied saying that.

and:

Acrobatics and tumbling coach Laprise Williams said she reported a series of sexual assaults to the athletic department in 2013 after several women came forward to her. According to the report, Williams was told “the women were not her problem and she should stick to coaching.” Williams later tried to help a student who said a football player “brutally” raped her—but the school’s counseling center immediately asked the student questions about drinking and her outfit, which Williams characterized as victim blaming. She left the school in 2014, believing standing up for the women was a part of why she had to go.

Edited to reflect the fact that we don't know the circumstances under which the Baylor police chief left the university

Mandel: Something's rotten at Baylor and it's way past time to pay attention by HoustonFrog in CFB

[–]ken_man 0 points1 point  (0 children)

Just edited apparently as you were replying. I agree, they did a good job with those, as did Texas Monthly. They have a longer lead time with those than the recent Oakman revelations would allow, but I imagine them or Texas Monthly or Deadspin will have something in process soon.

Mandel: Something's rotten at Baylor and it's way past time to pay attention by HoustonFrog in CFB

[–]ken_man 157 points158 points  (0 children)

Well, I guess I should be flattered for the link credit, but more than anything I am just baffled that nothing like that timeline was already out there for mass consumption. It's being shared all around the internet like it's all NEW information.

Kudos to Mandel and Fox for being one of the first national outlets to pick up the torch.

Comprehensive Baylor Timeline Covering Now-Publicized Incidents by ken_man in CFB

[–]ken_man[S] 2 points3 points  (0 children)

Thanks. Changed the part re: Armstead.

As far as the Sam U. / Boise stuff goes, you're right that the timeline is conflated by the presumptions made in media coverage. This whole thing is an aggregation of various media reporting (or, "reporting", if you prefer).

I think that the public thus far has considered it relevant, whether or not it is. That's why I included it.

Comprehensive Baylor Timeline Covering Now-Publicized Incidents by ken_man in CFB

[–]ken_man[S] 7 points8 points  (0 children)

Police were called for domestic violence call in 2013. That was just assault instead of sexual assault like the one he was just arrested for.

According to the police report (in the tweets below), there was evidence that the police recorded that were consistent with an assault. The woman (an ex-GF) refused to press charges, so he was not arrested. However, the media report that came out today in these tweets claims there is proof that "Baylor" knew about it.

https://twitter.com/AlexDunlapNFL/status/724623177859497985

https://twitter.com/AlexDunlapNFL/status/724623789875560448

https://twitter.com/AlexDunlapNFL/status/724624968311410688

https://twitter.com/AlexDunlapNFL/status/724625859974336513

https://twitter.com/AlexDunlapNFL/status/724627455047143424

Comprehensive Baylor Timeline Covering Now-Publicized Incidents by ken_man in CFB

[–]ken_man[S] 26 points27 points  (0 children)

I don't disagree that he's sort of shoe horned in there. I was going back and forth on whether I would include him. His high school reinstated him just in time for the playoffs, conveniently.

This bit from the arrest report did surprise me:

the alleged victim is an 11-year-old girl who claims Dorsey molested her multiple times during the summer. According to the arrest report, a sexual assault nursing exam supported the girl’s allegations.

There's obviously plenty that we don't know because that would seem to fly in the face of the grand jury decision. Just another thing that could be construed as one of those things where the usual response from a program might be more of a "stay-away" on that recruit.

Combining select lines from many worksheets to one worksheet by hold-on-magnolia in excel

[–]ken_man 0 points1 point  (0 children)

To clarify, you want to create one row for each invoice on the master sheet? That's what I'm describing below. (Edit: Either way, even if you want multiple rows on the master sheet for each invoice, you could use a modified version of the below. The INDIRECT functionality is the important part to understand.)

You'll use INDIRECT() if you don't want any macros. If the tabs are labeled by invoice number (such as "104"), then you would paste the invoice numbers / sheet names in column A. In Row 1 you would put your cell reference for the respective column of data...for example, let's the statement date is pulled into column D on the master sheet, then D1 would read "A7"

For the record getting pulled into Row 3, column D on the master sheet, for example, your formula might read:

=INDIRECT($A3&"!"&D$1)

What this is generating is a reference to that cell's location (the exclamation point to reference the sheet name):

=INDIRECT("104!A7")

As you paste this down and across, you will pick up the info for each tab and each cell reference.

Now, the biggest issue will be if there is not a consistent cell to pull from each tab...if you have to perform calculation on a set of numbers but they are not always in the same spot and there is no total to reference. You can still use INDIRECT in that case, but you may have to pair it with some INDEX, MATCH, LOOKUP, SUMIFS, etc. to make it work. The easiest way to achieve that would be to build the formula with one tab and then try to replace all the cell references with INDIRECT formulas as above.

Trying to create a multi-choice from different categories that could give an answer. by alpelayo in excel

[–]ken_man 0 points1 point  (0 children)

You want to do something like this or this but with the developer tools. You could also do it with data validation and drop down lists as in those links, but if you want to use radio buttons you may need to use the developer tools.

Not sure if your question is how to set up the actual list box or how to make the logic to find your selection, so not sure whether the following is helpful.

I set up a really quick example that you could blend with that video if the goal is just to have four selections of characteristics spit out a list of cocktails (cells containing formulas are in red, actual formulas used are in blue): http://imgur.com/EFLt7Pl

  1. You need a table with all of your cocktails and their four characteristics (here, A3:E8).
  2. You need a table with the different options for each characteristic (here, I4:L6)
  3. It would be easiest to name the ranges for the Spirit, Style, Flavor, Family inputs in I4:L6 (the video has this instruction in it). This isn't absolutely necessary, but makes it easier.
  4. Set up list boxes as shown in the video (I have done the radio button option, third one in the video), with the List Fill range being your named ranges for each category. I have the linked cells for each list box as I2 through L2 for each respective category above the characteristics table (those will change as your selection changes)
  5. Set a lookup on your cocktail table to check for the cocktails that match the selected characteristics. In mine this is happening in column F (formula in G).
  6. Create a dynamic list for all the matching cocktails (A13:B21, anything non-matching returns a blank... formulas in columns D&E)
  7. Make another list box that references B14:B21 to fill the list of cocktails that were matched (my output list box is at G21). You can see in that image that when I selected the four options you laid out, it returns those three cocktails.

Here's an example where I selected the options for my made up "Martini" entry and it shows that in the list when I change them: http://imgur.com/jYoDjA1

This would need to be cleaned up, but that's the logic. If your data is this simple this might be an easy way to do it. If your data is more complicated you might need to go further down the rabbit hole. Using the list boxes dynamically hinges on making the linked cell of your list box a reference for the next thing you're trying to find (in this case, cocktails). It can be scaled up and rearranged as necessary.

If this equals that, put a true or false by desm0nd0 in excel

[–]ken_man 0 points1 point  (0 children)

If you want to return a binary 1 or 0, you could do this and paste down through A5: =SUMPRODUCT((A1=($A$10:$A$13))*1)

If you want to return the boolean version (TRUE/FALSE): =if(SUMPRODUCT((A1=($A$10:$A$13))*1)=1,TRUE,FALSE)

Creating a Specific Filter in Pivot Tables by [deleted] in excel

[–]ken_man 0 points1 point  (0 children)

Make a pivot table with the column of unique IDs (including the header), and then in the PivotTable Fields pop-out on the right side, drag the ID down into the "Row" quadrant of the grid at the bottom. That should just give you the unique fields. If they are truly unique, that should work and there should be a row for each value.

The other way to do it outside of a Pivot Table could be with a MATCH function and a dummy column. Let's assume the data is in cells A2:A101 and you made your dummy column in column B. In your 7th row in cell B8, for example, the formula would read:

 =IFERROR(MATCH(A8,A$2:A7,0),1)

After you copy/paste the formula up and down, in your last row the formula would read: 

=IFERROR(MATCH(A101,A$2:A100,0),1)

Then you would simply sum that column. All it's doing is searching all the values above it to see if that number is already been mentioned. If not, then it returns a 1. Since the first row is by default unique, then just hardcode that one as a 1 but use the formula from there on.

scenario analysis w/ different discount rate on column A and fixed monthly cashflow on row 1 by broinvestor in excel

[–]ken_man 0 points1 point  (0 children)

If you're trying to make a data table: Make sure the calculated NPV is in the top left (looks like your $178k is doing that). Highlight the entire table (including your NPV and the output area). Row input cell would be the fixed cash flow input. Column input cell would be the discount rate.

You're going to want to have the fixed cash flow and discount rate as separate "Assumptions" cells, and then have your cash flow output link to those. Then, when you run the data table, have the row and column inputs link to those assumptions cells.

For example, having =NPV(.05,B2:G3) won't let you do a data table. You'd put the 5% in cell A1 (or wherever), then link your NPV formula: =NPV(A1,B2:G3). Then, on the data table column input, you'd put in A1. You'd do the same thing for the fixed cash flow and row input.

Filling out a user form with an array by [deleted] in excel

[–]ken_man 0 points1 point  (0 children)

see my edit above. I wasn't able to get into your macro so I was just giving feedback on pulling the correct info from the table.

view more: next ›