# Array index Excel

• How to Use the INDEX and MATCH Function in Excel
• Can Excel's INDEX function return array? - Stack Overflow
• INDEX Function - Formula, Examples, How to Use Index in Excel
• Excel INDEX function with formula examples
• Excel INDEX Function
• How to Use the INDEX Function in Excel
• Return An Array Using in Excel Using INDEX Function
• Excel formula: Return array with INDEX function Exceljet
• Arrays, Array Formulas, and Table Arrays in Excel
• Using INDEX to Extract a Value from an Excel Array Formula ...

The Excel Index function returns a reference to a cell that lies in a specified row and column of a range of cells. There are two formats of the function, which are the Array Format (which is the most basic format), and the Range Format of the function. These are described separately below. Array : It is the range or an array. Row number : Ther row number in your array from which you want to get your value. [optional column number] : This column number in array. It is optional. If ommited INDEX formula automatically takes 1 for it. Without anymore boring theory of INDEX function lets jump into an scenario. There are two versions of the INDEX function in Excel - array form and reference form. Both forms can be used in all versions of Microsoft Excel 2019, 2016, 2013, 2010, 2007 and 2003. INDEX array form. The INDEX array form returns the value of an element in a table or an array based on the row and column numbers you specify. But to answer your question, INDEX can indeed be used to return an array. Or rather, two INDEX functions with a colon between them: =INDEX(A1:A4,2):INDEX(A1:A4,3) This is because INDEX actually returns a cell reference OR a number, and Excel determines which of these you want depending on the context in which you are asking. =INDEX(LINEST(A2:A51,B2:D51,,TRUE),3,1) The LINEST results are nested within the INDEX function, where they act as its first argument. Excel expects INDEX to take an array of values as its first argument — parsing an array is what INDEX was born to do. The INDEX function is categorized under Excel Lookup and Reference functions. The function will return the value at a given position in a range or array. The INDEX function is often used with the MATCH function. We can say it is an alternative way to do VLOOKUP. As a financial analyst, the INDEX function can An array is a range or group of related data values.The values in an array are normally stored in adjacent cells. Learn how arrays are used in formulas and as arguments for specific functions, such as the array forms of the LOOKUP and INDEX functions. The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to... Surprisingly, INDEX function does not returns an array of values when we supply an array as indexes. We only get the first value. And SUM function returns the sum of one value only. So how do we get INDEX function return that array. Well I found this solution on internet and I don’t really understand how it works, but it works. INDEX returns #REF! if row_num and column_num don't point to a cell within the array. Related Excel Functions The MATCH function is similar to LOOKUP, but MATCH returns the position of the item instead of the item itself.

## Looking to speed up VBA code that generates possible lineups for DFS in excel. Currently using For Loops to do so.

2020.09.16 20:30 clutch1521 Looking to speed up VBA code that generates possible lineups for DFS in excel. Currently using For Loops to do so.

I have created the code below to generate daily fantasy sports lineups from a pool of players that have been entered in my excel file. Right now the code can run through about 16,500 combinations per minute. It takes too long to run through all possible lineups given the large amount of possible combinations. I'm hoping for someone to give me some advice on how to improve the code and/or cut down the run time. Also I am not looking to get a more powerful computer to run this code right now.
`
``'Declare Array Dim Results Sheets(1).Activate 'Declare Number of Positions and Players and Assign Dim QB As Byte, RB As Byte, WR As Byte, TE As Byte, Flex As Byte, Defense As Byte, Players As Long QB = Sheets("Chosen Player Pool").Range("A27") RB = Sheets("Chosen Player Pool").Range("F27") WR = Sheets("Chosen Player Pool").Range("K27") TE = Sheets("Chosen Player Pool").Range("P27") Flex = Sheets("Chosen Player Pool").Range("U36") Defense = Sheets("Chosen Player Pool").Range("Z27") Players = Sheets("Lineup").Range("B20") 'Create Results Array ReDim Results(1 To 50000, 1 To 9) 'Clear Lineups Sheet Sheets("Generated").Activate Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents 'Declare Counters Dim Rw As Single, i As Byte, j As Byte, k As Byte, l As Byte, m As Byte, n As Byte, o As Byte, p As Byte, q As Byte 'Start Counting, comment out For and next lines then set value to lock Sheets("Lineup").Activate ActiveSheet.Range("A2").Select 'QB For i = 1 To QB Range("A2").Value = i 'RB1 For j = 1 To RB Range("H3").Value = j 'RB2 For k = 1 To RB Range("H4").Value = k 'WR1 For l = 1 To WR Range("H5").Value = l 'WR2 For m = 1 To WR Range("H6").Value = m 'WR3 For n = 1 To WR Range("H7").Value = n 'TE For o = 1 To TE Range("A8").Value = o 'Flex For p = 1 To Flex Range("A9").Value = p 'Defense For q = 1 To Defense Range("A10").Value = q 'Salary Cap Check If Range("D15") = "No" Then GoTo SkipPaste 'Duplicates Check If Range("D18") = "No" Then GoTo SkipPaste If Rw = 50000 Then GoTo PasteResults Rw = Rw + 1 'Create Vector Array, Places Player into appropriate column Results(Rw, 1) = Range("C2"): Results(Rw, 2) = Range("C3"): Results(Rw, 3) = Range("C4"): Results(Rw, 4) = Range("C5"): Results(Rw, 5) = Range("C6"): Results(Rw, 6) = Range("C7"): Results(Rw, 7) = Range("C8"): Results(Rw, 8) = Range("C9"): Results(Rw, 9) = Range("C10") SkipPaste: 'Defense Next q 'Flex Next p 'TE Next o 'WR3 Next n 'WR2 Next m 'WR1 Next l 'RB2 Next k 'RB1 Next j 'QB Next i 'Paste Results PasteResults: Sheets("Generated").Cells(1).Resize(UBound(Results, 1), UBound(Results, 2)) = Results Sheets("Generated").Activate ` ``
To add some more background on what is going on in the excel file...
On the first sheet of the file ("Lineup", where most of the work is being done), I have Index(Match()) functions to look up the player, salary and team information based on the iteration of the loop. I then have a check to make sure that the salary fits into the constraints of the rules (Cell D15). I then make sure that players are not repeated as they could be shown to repeat due to the nature of my loops (Cell D18). Please let me know if there are any additional questions you need to help me out. TIA!

2020.09.16 06:06 thetorontobot Toronto Daily - Sep 16th 2020

# Welcome to the Toronto Daily Thread.

1) To collect and make visible new posts in smaller Toronto based subreddits.
Feel free to visit, comment and be generally helpful in posts indexed below. Please also remember to stay on your best behaviour when travelling outside of /toronto.
2) To act as a general off-topic conversation hub for the day.
To that end, use this thread to talk about whatever is on your mind, regardless of whether or not it's related to Toronto.
No matter where you're posting, please remember to be excellent to each other.

## /TorontoEvents

Private, socially-distanced concerts in Toronto socialdistancing716 5

## /GTAMarketPlace

[SELLING] ASSASSINS CREED VALHALLA PC KEY \$60 Sinnclaire 1
Men's Ultraboost 20 Size 10.5 - Brand New In Box with tags - \$140 methlabz 0
I am a bot, and this post was generated automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2020.09.14 17:24 DoraDD How to use INDEX and MATCH in an array fornula to create a list?

Hi all!
I'd really appreciate your help with making this work. First off, here's a mock-up of my sheet so I can make this easier to understand.
In Sheet 1, this is Table 1 called tbl_Table_1 for this purpose. In reality, it has over 15 columns. These are just the ones I'm concerned with at the moment. The "Participants" column is column 13 in the original table (hence the column 13 reference in the formula), and the exhibition name is column 2 in case you need to know.
Exhibition name Participants
Exhibition a Participant 1, participant 2, participant 3
Exhibition b Participant 1, participant 4, participant 5
I have a large dataset listing exhibitions, participants and other data. I have created a dropdown list of all possible participants (e.g. Participant 1, participant 2, etc.) and now want to extract all the exhibitions they've taken part in. The issue here is that, when I apply the following array formula in Sheet 2, I end up getting the same exhibition name repeated over and over again. I want a list of all the exhibitions any individual participant has taken part in.
Here's the formula I'm using (adapted for this mock-up) in Sheet 2:
=INDEX(tbl_Table_1[#All];MATCH(INDEX(tbl_Table_1;MATCH("*"&Participant 1&"*";tbl_Table_1[Participants];0)+1;13)tbl_Table_1[Participants];0)+1;2)
Mind you, I get the data I want using this formula, but Excel stops at the first exhibition name and just repeats it in all cells in the array formula.
The bolded part of the formula might seem strange to you and like it has errors because of the "+1" but it seems to get me the data I need. So unless you think this is causing the issue, you can completely disregard it.
So thank you for taking the time to help me out here!
If need be, I can create a more realistic mock-up in Google Sheets, but I'm unable to share the exact spreadsheet since it has data I can't go public with.
Thank you again!
Cheers!

2020.09.14 14:53 excelevator UDF - MULTIFIND ( find_range , words_range [, case_match , match_all , return_all ] ) - return a string/s from multiple search words

`UDF - MULTIFIND ( find_range , words_range [, case_match , match_all , return_all ] )`
`MULTIFIND` allows for the return of a string or multiple strings from a search list, matched from a list of words the user enters as criteria.
The advent of Dynamic Ranges in Excel 365 gives the opportunity to write UDFs that can return multiple array results in an easy manner where they Spill automatically; in this UDF with the `return_all` option set in the last argument.
The other option for pre Dynamic Ranges is to select a range of cells and enter the formula as an array formula with ctrl+shift+enter.. but this was written with 365 and dynamic ranges in mind.
`find_range` - the contiguous range of cells with the values to search in for a match
`words_range` - the contiguous range of cells with the search words in , one per cell.
`case_match` can be set to `TRUE` to return case matched values. `MULTIFIND` is not case sensitive by default.
`match_all` can be set to `TRUE` to only return a matched value when all the search words have been entered into the `word_range`. By default `MULTIFIND` starts to return matched values on the first search word as entered, returning more filtered values with each additional word,
`return_all` can be set to TRUE to return all matched values in an array. By default `MULTIFIND` will return the first matched result.

# Examples

Source list Example 4 word search list range1 Example 4 word Search list range2
An apple a day SNOW garden
A banana go away peas my
Snow Peas For Winter carrots
Carrots in my garden in
Lots of snow peas in winter
Carrots planted in my garden
Who planted carrots in my garden
Runner beans be running
Formula Example 1 results Why
`=MULTIFIND(A2:A9,B2:B5)` Snow Peas For Winter default
`=MULTIFIND(A2:A9,B2:B5,1)` #VALUE! case senstive
`=MULTIFIND(A2:A9,B2:B5,,1)` #VALUE! match all words
`=MULTIFIND(A2:A9,B2:B5,,,1)` Snow Peas For Winter array
`array` Lots of snow peas in winter array
Formula Example 2 results Why
`=MULTIFIND(A2:A9,C2:C5)` Carrots in my garden default
`=MULTIFIND(A2:A9,C2:C5,1)` Who planted carrots in my garden case matched
`=MULTIFIND(A2:A9,C2:C5,0,1,1)` Carrots in my garden match all words, return all words
`array` Carrots planted in my garden
`array` Who planted carrots in my garden
Paste the following code into a worksheet module for it to be available for use.
``Function MULTIFIND(t As Range, f As Range, Optional cSen As Boolean, Optional fAll As Boolean, Optional rAll As Boolean) As Variant 'MULTIFIND ( find_range , words_range [, case_match , match_all , return_all ] ) 'https://www.reddit.comexcelevator 'https://old.reddit.com/excelevator 'https://www.reddit.com/excel - for all your Spreadsheet questions! Dim tArray() As Variant tArray = t Dim fArray() As Variant fArray = f Dim aString As String fSize = UBound(fArray) Dim wCount As Integer Dim tValue As String If Trim(Join(WorksheetFunction.Transpose(fArray))) = "" Then Error (13) For Each ta In tArray wCount = 0 tValue = IIf(cSen, ta, UCase(ta)) For Each fa In fArray If Not cSen Then fa = UCase(fa) If InStr(tValue, fa) And IIf(fAll, fa <> "", True) Then wCount = wCount + 1 Next If wCount = fSize Then aString = aString & ta & "§": If Not rAll Then Exit For Next MULTIFIND = WorksheetFunction.Transpose(Split(Left(aString, Len(aString) - 1), "§")) End Function ``
Let me know if you find any bugs!

# See a whole bundle of other custom functions at Excelevator

2020.09.11 10:41 bhaveshj21 converting excel to struct

Hi,
I have a use case where I need to read an excel and process it , which will be uploaded by the user.
I am using github.com/360EntSecGroup-Skylaexcelize this package to read the excel , the problem here is that to reach value of each cell I need to read the row as array and cell with the index , this will hardly bind the column sequence and will fail if the sequence is changed, I was thinking is there a way to read excel directly to a struct.
I found this https://github.com/szyhf/go-excel , but the issue here is it requires the file path to open it,
I receive the file in request which is *multipart.FileHeader format , here for this I need to first create a file and save it somewhere and then process it .
is there any other way where I can convert excel into struct .

2020.09.11 02:07 MCP_ Come and Join the Simulation Hockey League!

Find us at: https://simulationhockey.com/index.php
Hello Buffalo Sabres fans! First we would like to thank the mods for allowing us to share our league with you!
So what exactly is the Simulation Hockey League? We are a free, established sim-league about to enter our 56th season! The SHL allows you to create a character and take control of every aspect of their career. From creation there are tasks you can participate in on a weekly basis to earn points that go towards improving your player to perform better. With the conclusion of our most recent season comes the off-season activities, including the Simulation Major Junior Hockey League's (SMJHL) Entry Draft, the initial stepping stone for your player. You will be drafted to one of 12 excellent SMJHL teams, including 2 brand new expansion teams! You'll spend your first season getting acclimated to the league and fighting for the Four Star Cup, and afterwards you'll be drafted by one of the 18 Simulation Hockey League (SHL) teams. Work hard, stay active and you could earn the prestige of being the 1st overall draft pick!
BE-A-PRO EXPERIENCE - A rich, personal hockey experience that crosses between video games and fantasy hockey. Build your player exactly how you want with a variety of different styles. Be totally involved in your player from the beginning, and take them from rookie to the SHL Hall of Fame! Every game is simulated on Franchise Hockey Manager 6, and its wide array of attributes allows you to customize your player to the maximum. Success is based on your activity within our community, so the more involved you are, the greater your player can be! A link to our YouTube channel can be found here if you want to see what our daily streams are like! https://www.youtube.com/channel/UCbU8U2aA6WyUCIgYthiqqbQ
FREE - As stated earlier, I want to emphasize that our community/site is completely free. It's free to join, free to participate, free to do what you want. Our league runs off the hard work of our community, we all serve each other in that regard. Every aspect of our site is run by our members. This isn’t a sales pitch, we’re just always looking for new members to fuel the already great community we have built!
DRAFT DAY - Experience what it is like to wait for your name to be selected, alongside your draftmates, vying for that top spot! This is a full draft experience where real people will draft you and spend their resources on your potential. Become the next big steal, or potentially the next big bust. Show the GMs you’ve got what it takes to be the face of their franchise!
REAL PEOPLE, REAL OWNERS - Every aspect of the league is run by real people from all around the world! Your draftmates, future teammates, general managers and the league’s head office are all real people you can interact with. Every decision from team lineups, draft day selections and last minute trade deadline deals are done by teams owned by members of the site, with plenty of opportunity for you to fill in those roles yourself! SHL superstar, team captain or scout, General Manager, Head Office; the site is full of opportunities for you to get involved however you so choose!
COMMUNITY - Once your player is drafted onto a team you will gain access to a private locker room for your organization, and their Discord server (should you choose to join there). Here is often where the closest relationships develop, and where you’ll make some great new friends to share your SHL journey with! You can spend time discussing the season, reflecting on your success, or just having a blast hanging out together! Of course you’ll find passionate hockey fans here, but with so many unique members, you’re bound to find people with lots of interests all over the site for you to get connected with.
Find us at: https://simulationhockey.com/index.php

2020.09.09 21:18 DeathToHeretics Check out the Simulation Hockey League!

https://imgur.com/JYvKbKv
Find us at: https://simulationhockey.com/index.php
Hello Minnesota Wild fans! First we would like to thank the mods for allowing us to share our league with you!
So what exactly is the Simulation Hockey League? We are a free, established sim-league about to enter our 56th season! The SHL allows you to create a character and take control of every aspect of their career. From creation there are tasks you can participate in on a weekly basis to earn points that go towards improving your player to perform better. With the conclusion of our most recent season comes the off-season activities, including the Simulation Major Junior Hockey League's (SMJHL) Entry Draft, the initial stepping stone for your player. You will be drafted to one of 12 excellent SMJHL teams, including 2 brand new expansion teams! You'll spend your first season getting acclimated to the league and fighting for the Four Star Cup, and afterwards you'll be drafted by one of the 18 Simulation Hockey League (SHL) teams. Work hard, stay active and you could earn the prestige of being the 1st overall draft pick!
BE-A-PRO EXPERIENCE - A rich, personal hockey experience that crosses between video games and fantasy hockey. Build your player exactly how you want with a variety of different styles. Be totally involved in your player from the beginning, and take them from rookie to the SHL Hall of Fame! Every game is simulated on Franchise Hockey Manager 6, and its wide array of attributes allows you to customize your player to the maximum. Success is based on your activity within our community, so the more involved you are, the greater your player can be! A link to our YouTube channel can be found here if you want to see what our daily streams are like! https://www.youtube.com/channel/UCbU8U2aA6WyUCIgYthiqqbQ
FREE - As stated earlier, I want to emphasize that our community/site is completely free. It's free to join, free to participate, free to do what you want. Our league runs off the hard work of our community, we all serve each other in that regard. Every aspect of our site is run by our members. This isn’t a sales pitch, we’re just always looking for new members to fuel the already great community we have built!
DRAFT DAY - Experience what it is like to wait for your name to be selected, alongside your draftmates, vying for that top spot! This is a full draft experience where real people will draft you and spend their resources on your potential. Become the next big steal, or potentially the next big bust. Show the GMs you’ve got what it takes to be the face of their franchise!
REAL PEOPLE, REAL OWNERS - Every aspect of the league is run by real people from all around the world! Your draftmates, future teammates, general managers and the league’s head office are all real people you can interact with. Every decision from team lineups, draft day selections and last minute trade deadline deals are done by teams owned by members of the site, with plenty of opportunity for you to fill in those roles yourself! SHL superstar, team captain or scout, General Manager, Head Office; the site is full of opportunities for you to get involved however you so choose!
COMMUNITY - Once your player is drafted onto a team you will gain access to a private locker room for your organization, and their Discord server (should you choose to join there). Here is often where the closest relationships develop, and where you’ll make some great new friends to share your SHL journey with! You can spend time discussing the season, reflecting on your success, or just having a blast hanging out together! Of course you’ll find passionate hockey fans here, but with so many unique members, you’re bound to find people with lots of interests all over the site for you to get connected with.
Find us at: https://simulationhockey.com/index.php

2020.09.09 18:12 OpheliaCyanide [The Extramundane Emancipation of Geela, Evil Sorceress at Large] --- Chapter 29: The Impenetrable Celestial City (Comedy Fantasy)

Synopsis: After hoodwinking Darkos, a holy priest, into escorting her back to her castle, Dark Enchantress Geela has one item left on her list: revenge on her ex-husband. With a confused Darkos in tow, she sets out. However, Geela isn't the only one with secrets. And Barney isn't the only old enemy who's about to get a visit.
Index Previous Chapter Next Chapter
Patreon TalesByOpheliaCyanide
“Thieves!” The shout echoed behind Geela and Darkos as they blitzed out of the little encampment like their pants were on fire.
“We didn’t steal anything!” Darkos hollered back.
“Vagabonds!” the woman screamed.
“Rude,” Geela said, her mutter more aimed at Darkos than the trader.
“Murderers!” her husband shouted
“Well now that’s just verifiably untrue!” This time her comment was aimed at the unfortunate couple who had caught a sleeping Geela and Darkos entirely unaware about seven minutes ago.
“Stay away, foul stowaways!” The words were gasping pants as the old couple lacked Darkos’s strong legs and Geela’s artificially youthful lungs.
“Ok they do have us on that one,” Darkos said. The pair kept running for another few minutes until they had left the little campfire in the dust.
“Ugh.” Geela collapsed on the ground, more out of drama than exhaustion, though her burning ankles did contribute. “Darkos. Darkos why is life hard?”
He sat next to her and tucked his knees under his chin. “I’ve wondered that a lot myself.”
“Oh you have, have you.”
He nodded, not catching her tone. “Yeah. Sometimes I feel like the worst thing that ever happened to me was you finding me.”
He really knew how to cheer a girl up.
“I’d stopped at the Pillory and Stocks Inn for the night and in comes a classic fair maiden, crying about her uncle’s death.” He sighed and shook his head. “I really was naive and oblivious back then.”
Considering the death glares he was missing from Geela, it stood to reason he may still be oblivious.
“But then I think of everything that’s happened, all the craziness, the things I’ve learned about myself and Alerion and Noire and… I think I am ultimately happy that this all happened, even if it was the hard route.” He stared up at the full moon above them. “And I think that’s kind of poetic in a way.”
Geela’s lashes were flicking up and down, hard, as she stared at Darkos. “How could that possibly be poetic? Do you know how poems work?”
Darkos was quiet for a good while, ostensibly contemplating this, though Geela couldn’t be sure if he’d just fallen asleep. “I thought,” he started, “that poems are deep words phrased in ways difficult to understand.”
Geela didn’t know whether to groan or laugh or sigh, so she compromised on a mix of the three, which earned her an alarmed look from Darkos.
“What!” he said. “That’s how it’s always read to me.”
She opened her mouth to find the exact right way to tell him why he was wrong but soon found that there was no easy explanation. In fact, she wasn’t even sure if she disagreed with him. Barney used to write her poems and they never really made much sense. It was always a ‘thought that counts’ kinda thing, and since what Geela wants, Geela gets, it was nice to have someone who could give her something she wouldn’t be able to otherwise take.
Like headaches, mistakes, and heartbreaks. She never would have been able to get those on her own.
“Honestly,” she said, words finally filling her agape mouth, “that’s not the stupidest thing you’ve ever said. Close but you have said stupider.”
His face drooped a bit.
“Oh don’t flash those eyes at me,” she snapped. “You just said the day we met was the worst of your life. I don’t need it.” She reached into her bag and pulled out a map and a lantern, which she lit with a flick. “Let’s figure out how far we are from the Celestial City. You know, so we can kill another one of Noire’s children and save your doomed soul?” She could have added more but left it there. He’d do the extrapolating.
“That’s us there?” he asked, pointing at the shimmering arrow that said ‘You are here!’
“Nope.” Her eyes scanned the passage from the arrow to the city. They’d get there by dawn if they walked but damn did that sound like a bad idea.
“Then where are we?”
“Brain, Darkos. Use it.”
“Well,” he protested, “if this was a regular, non magic map, it wouldn’t make much sense for the arrow to know where we are. Besides,” he said, noting her cutting glare, “you did say nope.”
“I was just… nevermind. If we start walking there, we’ll arrive at the gates by dawn.”
Darkos sighed. “I was in the middle of a really good dream and sandwiches and a beach.”
Geela crawled to her feet before helping Darkos up. “Sandwiches at a beach?”
“Yeah,” he said as the two started moving. “There was sand in my sandwich.”
“What a tragedy.”
“Really? You don’t like sand in your sandwich?”
Geela looked over at him to see if there was a shred of sarcasm on his face. It was, however, irritatingly genuine.
“Why do you like sand on your sandwich?”
He smiled peacefully. “The grit in the nut paste makes it nicer to chew. Gives some resistance. We called ‘em sandywiches.”
Oh to have so little worries on one’s mind. “I suppose I never thought of that. I guess it makes sense.”
He made a noise then, like a suppressed snicker, and she looked over to see him fighting a smirk. “Really? Because it’s absolute ridiculous if you ask m—”
“Darkos!” she said. “It’s not funny when you do that!” But it was funny and she wasn’t able to stop from cackling a bit herself at the image of Darkos mowing down on sandywiches. “In my defense, your village is weird. Like really weird.”
“We also didn’t have beaches. I only got to see those when I first went out on my missions.”
Darkos frowned. “I, uh… I’m not sure. I actually don’t know where he lived before Sunnyville. I never—”
“Thought to ask.” But she didn’t push it any further. She didn’t want Darkos questioning his past. That was too dangerous. “I haven’t been in years. Not since before the Barney blowup.”
“S’pose we might have to go there to track down a child of Noire’s? I’d like to see the ocean again.”
“I don’t know. I can’t say much of anything.” She didn’t like being in the dark and their path had so many uncertainties. “We’ll see when we track down Professor Elle.”
“What’s her connection to Noire again?”
“She worked at the Academy and leads the Realm Studies department. If there’s anyone who’s going to know where some horrible, evil, dark deeds are happening, it’ll be her. Or at least she’ll know where to point us.” It was a bad excuse, really. But Geela couldn’t say ‘well, when your mom learned you were void spawn, she told your parents to pack up and join a cult to the very same void fiend that brought you into existence.’ And Darkos didn’t contest her reasoning as he switched the conversation back to his dreams and the two fell into easy conversation.

Geela needed a nap and a bath and a real meal so badly she could cry. But she didn’t because Darkos was already being sweet enough to carry her the remaining quarter mile into town and he was potentially feeling the hours of walking worse than she was. She was technically older but he had been carrying her for the past ten minutes. So she could stave off her misery until the two could share in it more evenly.
Finally they rounded the top of the final hill and there the Celestial City sprawled ahead of them, a massive metropolis, miles across and miles deep and a mile high in some places. Absolutely no exaggeration. It was enormous.
As large as it was, however, Darkos’s jaw had opened wide enough to probably engulf the whole city if he’d chosen to. She didn’t know his mouth could get so big, almost as huge as his eyes. He almost looked ready to dislocate his jaw.
“Geela!”
She smiled, quite pleased with the effect it had on him. The whole thing shone, even in the early glints of the morning light. It was all chrome and metal, gold and silver and platinum and steel and copper and bronze and titanium, coating everything. Dazzling spires shot up on all sides, some reaching thousands of feet tall. Massive towers stretched upward, some as wide as they were tall. They were too far away to see the air traffic of the city, but Geela knew that, as they approached, they’d see the exotic array of wild birds that acted as delivery and message deliverers or, if you were extremely wealthy and powerful, transportation.
In the center was the crowning glory. Not the city’s crowning glory of course. Geela’s. For in the middle of the megacity stood the most absurd building anyone could ever lay eyes upon. It’s construction didn’t make sense; it was as if half the building had simply vanished and instead of rebuilding what had disappeared, they’d just constructed new towers and turrets and rooms and hallways and staircases and chambers and courtyards, all in the void where the second half should be, all supported by rickety looking scaffolding.
“See that building in the middle? That's the academy. That’s my handiwork.”
He looked over his shoulder at her. “You didn’t build it.” He was certain and Geela gave him props.
“No indeed. See how strange the second half looks?”
“Like something went missing and they rebuilt it but just the interior?”
“Excellent analysis.”
“Did you build that part?”
“Oh no. Oh no, dear Darkos. I didn’t build that ramshackle half.” She rubbed her hands together. “But I am the reason it needed rebuilding.”
“Oh man. Oh man I forgot you collapsed it after destroying your cult.” He rubbed his head. “I just pictured it so much smaller when I first heard that story. Geela how tall is it?”
“The half that’s missing had the astronomy tower, so with that it was about four thousand, three hundred, and ninety two feet.” She knew precisely how tall it was. One must always record one’s successes.
He gave a low whistle. “Why’d you take the academy out along with the blood cult?”
“The dissolution went poorly. And then we were… found out. Another story for another time. We need to get in that city and we need to take a well deserved rest.”
“Well, you’ve got my buy in.” He hoisted her up a bit higher on his hips and then pointed forward. “Celestial City ho!”

They reached the entrance just as the sun fully blessed their faces with its scorching rays. The massive gates were locked tight.
“Hello there!” Darkos shouted up the gates to the hypothetical guards at the top. “We, uh, we’d like to request entrance to the city?”
For a moment, nothing happened. Then something was thrown over the wall and Darkos and Geela tumbled out of the way.
“Ow!” Geela had managed to land funny on both ankles and a wrist. “What in all the realms was that?” She stumbled back to her knees, looking to see a bucket still swinging on the end of an absurdly long rope. “Really?” she yelled up the wall. “A bucket? A bucket is the kinda of top tier security in this damn place?”
As her voice shrieked up the walls to some faceless, soulless guard, Darkos investigated the bucket.
“It says ‘ID please’ on it. Has a stampbook too.”
Geela’s head snapped over to the bucket. “IDs? I don’t have an ID.”
Darkos reached into his pocket. “I do, actually. Got one on my travels.” He pulled out a small stiff piece of parchment. “Wait, how do you not have one? You lived in the city for years.”
“Yeah, as Geela Scilatia.” She gestured at herself.
“Ok, but you’re known as Ja’Eel. Wouldn’t they just think you were named after her?”
Geela stared at the bucket as if the twisted piece of tin had directly offended her. “Yes, when thinking of the evil sorceress, they think Ja’Eel. When the people of the Celestial City hear ‘Geela Scilatia’ they think the woman who blew up the academy.”
Darkos nodded and then froze, mid nod. “And you never got a fake?”
Geela’s lip jutted in a pout. “I never needed a fake cause ‘Geela’ worked just fine and I never thought I’d come back here.”
“Not even to, like, rain hellfire on them for banishing you?”
She shifted uncomfortably. “Noooo… I did come back and get some personal revenge but after that I just kinda, you know. Let it lie. Some bad memories in there.” The idea of visiting her old town, her old school, had sat in her stomach through the whole cult business as barely a thought. It sometimes rumbled in her stomach at night, like an undigested bit of meat, but it was mostly absent. Then in the cart ride over, she’d gotten a bit more twitchy about it but again, didn’t let it phase her.
Now she stood at the gates and all the glee she’d felt upon first seeing the academy was long gone. It wasn’t like any of her old classmates would be there. It had been decades. They were probably dead. They’d better be. And if they weren’t, what were the odds that there’d be an abundance of septuagenarians chilling around?
Geela was tempting fate with her line of thought and Darkos was still waiting for her to come up with a grand suggestion.
“You can’t edit an ID, can you? With magic?”
She shook her head. “Literally any magic cast on an ID displays on it pretty dominantly. It’s, like, the main protective feature on the cards.” She fished out her own ID and looked at it, at her shiny thumbprint and name. If she put this in and stamped her thumbprint into the book, they’d be able to identify her straight away. And she had one of those lovely ‘kill on sight’ warrants on her, so there’d be no talking her way out of it.
“How hard is it to make a fake?” Darkos asked.
Geela’s lips pulled down. “Not hard. But it takes forever. Forever.” She knew she wasn’t being helpful but this was a difficult nut to crack. “I suppose we’ll just have to though. I’ve got the material but we’re gonna sit out here for a few hours. Find somewhere comfortable to sit.”
“Someplace cool too,” he said, wiping the back of his neck. He tugged twice on the bucket, signaling the brainless drones at the top to take it back up, empty.
“Cool. Funny.” She wrinkled her nose. “We’re in the Celestial Region, Darkos. There is no ‘cool’. It was only comfortable in the crate cause I had a little ice drifter going.”
He looked at her, then at the sun, and then quickly back to her because looking at the sun is always a bad idea. “Oh.” He blinked rapidly, vision no doubt filled with sunblots. “Ok. Right. Can you get that drifter thing going again?”
Geela nodded over to one of the rocky spires nearby. “Kinda. I can get it going once we’re in the shade.”
Without an enclosed space, an ice drifter isn’t too handy. The cooling mist drifts too fast. But they didn’t have another choice unless Geela wanted to summon a winter storm, so they huddled around her sputtering cube of everfrozen ice while she scratched at a sheet of paper.
“And how long will this take?” Darkos asked after approximately eighteen freaking seconds.
“Twelve hours. I can’t get it faster. I’ve tried.”
“Shoot. I mean, right. Ok. Shoot.”
They’d had two and a half days hiding in a crate on the back of a wagon, all time wasted, and now here she was, enchanting a piece of paper, barely hidden from the gates. In her defense, the gates hadn’t been ID only when she’d been there. This blindsided her.
After some period of time, as the sun reached higher in the sky, probably closing in on noon, Darkos started fidgeting, bouncing, squirming, trying to get her attention.
“Just go behind that rock over there.” She nodded towards a pile of boulders, eyes still on her work. “I won’t look.”
“No. No Geela, look. It’s them! The traders.”
Geela’s eyes flicked up in an instant. It was. It was the traders.
“We attack them,” she said, “take their IDs, render them unconscious, and cut their thumbs off—”
“Oh come on, there’s gotta be a better way of transferring their fingerprints.” Darkos looked back at the wagon making its way closer to the city. “They’re jerks but we were stowing away.”
“She called me a vagabond! Besides, you can just reattach it.”
Darkos nodded, now enthusiastic. “Sounds good. I’ll use my spooky Noire bestowed powers to fix her thumb.”
“Scratch that idea.”
“We’re scratching the ‘cutting off thumbs’ bit. Isn’t assault and identify theft bad enough?”
“Thumb lopping is easier.” But she agreed to his edit of the plan, which involved copying a thumbprint and making a fake thumb. It would take a few minutes more but it would be less messy.
The plan was simple. A disguised (just a hood, no need to get fancy) Darkos would exit the outcropping and make his way to the wagon where he would ask them to help his friend who had fallen and needed, well, help. If they accepted, Darkos would bring them to where Geela lay in wait, and she’d jab them with somnus pins. If they refused, Darkos would trap them in a force field using his ring of fields and Geela would hurry over and jab them with somnus pins. This was a tad riskier cause it’d be out in the open but both should work.
Luckily they didn’t have to worry cause the old couple was just stupid enough to follow Darkos into a shady grouping of rocks where Geela launched herself at them in an altercation that probably could have gone a lot smoother.
Still, only thirty minutes after taking down the traders, Geela was the proud owner of the ID of one Thisslina Jade.
“Gorgeous name for such an ugly person,” Geela said, putting the finishing touches on her fake thumb. It may have taken magic to make but it was completely mundane in its own rights. Wouldn’t trigger a flair on the stamp pad. “Alright let’s make like a leaf and…”
“Close,” Darkos said. “But let’s instead make like a drum and beat it.”
Geela would have to remember that.
They hurried back to the gates where Darkos looked back up and hollered, “Hello up there!”
A moment of nothing and then—
The doors swung open. They bloody swung open.
Geela’s face twisted sour and she stormed to the guard right on the inside. “You there,” she said, her voice harsh. “Why the hell did that gate open?”
The guard’s face remained unreadable as he responded. “Did you knock or otherwise announce your presence?”
“Yes. We shouted.”
“That would be why ma’am.”
Oh no, he wasn’t getting away that easy. “Earlier they asked for our IDs. This time they let us in. What kind of backwards rules are you adhering to?”
He didn’t flinch. “Apologies for the confusion. Anyone arriving at the gate before 7 AM must submit an ID. After that, the gates are open to anyone.”
Geela was shaking. Her fingers trembled so hard she had to clench them into balls. She wheeled away to see a nervous Darkos.
He nodded. “Uh. Are you gonna do something about it? Like burn someone or something?”
Oh did Geela want to say yes. But an elaborate revenge plot on the door took time they didn’t have so she just lifted her shoulder and her chin.
“No. I’m deciding to be the bigger one here.” It was still burning in her mind and probably on her face, but she didn’t need to tell Darkos. “Now,” she held out her hand, which he took on his forearm, “let’s go find this Professor Elle.”
New faces, new places, new... prosthetic thumbs. Oh well, Geela, can't win 'em all.
Any predictions on what'll happen inside? Impatient to find out? Check out my Patreon! We're \$1 dollar away from the goal I need to get all the artwork done. Yes, this includes the Miss Potato Princess Pagent dress. So check it out!
See you all Friday!

2020.09.09 11:09 Machiavillian Trying to match 3 criteria with a MATCH and INDEX fomula.

Hey guys,
I have a question using MATCH (VERGELIJKEN) and INDEX. I want to look up a value in a Matrix based on 3 different criteria that have to be matched. This requires a formala with array function. Below the fomula:
=INDEX(Prijslijst!A3:D42;VERGELIJKEN(1;(Prijslijst!A3:A42=A2)*(Prijslijst!B3:B42=A2)*(Prijslijst!C3:C42=A2);0);4)
Every component of the formula seems to work except this portion:
(Prijslijst!A3:A42=A2)
Using an '=' results in a false value indication for this part. I am using the Dutch version of excel, don't know if that has any impact on the formula? If I just plug one value in the formula instead of the "=" part, it works.
What do I need to change to make it work.

2020.09.07 16:30 jbkiddo INDEX and MATCH array with multiple criteria to find most common text in 170,000 rows returns "0" . Solution or alternatives?

My spreadsheet generates a report based on datasets uploaded by the user via macros that copy and paste data from separate CSV files, which are in a fixed format. Formulas are not edited and the datasets are pasted onto the blank sheets 'Citation Data' and 'Citable Items Data' each time. Here is a screenshot of the ~170,000 dataset on the 'Citation Data' sheet.
I need to find the most frequently occurring text in 'Citation Data'!C when the value in 'Citation Data'!D in the same row matches 'Citation Data'!A1. My current formula is:
`{=INDEX('Citation Data'!C\$6:C\$167819,MODE(IF('Citation Data'!D\$6:D\$167819='Citation Data'!A1,MATCH('Citation Data'!C\$6:C\$167819,'Citation Data'!C\$6:C\$167819,0))))}`
This has worked fine for smaller datasets (~2,000 rows), but now I need to apply it to a dataset with ~170,000 rows and the formula only returns "0". Does this mean the array is still processing data or that the formula is not functional?
I am using Excel 2016 and consider myself an advanced beginner. Happy to use VBA. Any suggestions or support would be much appreciated!
Update: ended up using a pivot table per the advice here, adding a pivot refresh function to the VBA of the initial data import macro so that those using the spreadsheet to create a report would not need to complete an additional step.

2020.09.07 11:21 joshmaxd Index Match, Match but return multiple results

Hi all,
I have an array formula I'm using to do an index Match Match right now:
``=INDEX(Table2[Custom Data], MATCH(1, (Table2[Custom Field Name]="Supplier-GXP Designation")*(Table2[InfoCard Number]=[@[InfoCard Number]]),0)) ``
Which is working fine. But I have noticed that some of my data in my lookup table has multiple entries, so my InfoCard number (the unique identifier for my entries) could be listed more than once with different "Supplier-GXP Designation"'s.
I can find resources with how to do index match, match, and how to have multiple results entered into a single cell with an Index Match, but haven't been able to work out how to combine the two?
Can someone help? I think it is something to do with the Agregate function but I'm not quite clear.

2020.09.07 08:41 heateh Alternative array match/index wildcards question, and possibility on multiple results in one cell

Good Day All,
Using Excel 2016, and trying to figure out a way to pull garage location data from a second data sheet based on multiple criteria. Criteria 1 and 2 are always the same e.g. manufacturer = ford, and model = focus. from their respective columns, but the 3rd criteria could be any of a few dozen garages e.g. GAR01 to GAR(insert number) from 1 same column on the 2nd data sheet.
Tried using the match/index array formula below, but can't use a wildcard with that for the "GAR(XX)" portion. What could be done in this case?
{=INDEX(DATAPULL!CJ:CJ,MATCH(1,("FORD"=DATAPULL!H:H)*("FOCUS"=DATAPULL!W:W)*("GAR01"=DATAPULL!CJ:CJ),0))}

https://preview.redd.it/c096b7qn8ol51.jpg?width=644&format=pjpg&auto=webp&s=4721b5123fcd1b3209280a37aae5819c851968d7
For my second part of the question: Is it possible in-case that a particular manufacturemodel is in multiple garages, that those results can be displayed in one cell e.g. location results is GAR04, GAR26 for a ford focus.

2020.09.06 13:48 flabby_abs Why am I getting "RecursionError: maximum recursion depth exceeded while calling a Python object" when I run this code?

I'm trying to implement a Decision Tree Classifier from scratch on a very small dataset.
``import numpy as np import pandas as pd from pprint import pprint class Classifier: # def __init__(self, train_data, features): # self.train_data = train_data # self.features = features @staticmethod def find_entropy_node(data): entropy = 0 _, counts = np.unique(data[:, -1], return_counts=True) total = np.sum(counts) for count in counts: probability = count / total entropy += -probability * np.log2(probability) return entropy @staticmethod def find_entropy_feature(data, feature): entropy = 0 _, counts = np.unique([data[:, feature]], return_counts=True) total = np.sum(counts) for count in counts: probability = count / total entropy += -probability * np.log2(probability) return entropy def find_best_feature(self, data): info_gain = [] entropy_node = self.find_entropy_node(data) for i in range(len(data[0]) - 1): e = self.find_entropy_feature(data, i) info_gain.append(entropy_node - e) return info_gain.index(max(info_gain)) class Tree: def __init__(self, train_data, features): self.train_data = train_data self.features = features self.g = Classifier() self.tree = {} def build_tree(self, table): parent_node = self.g.find_best_feature(table) feature_values = np.unique(self.train_data[:, parent_node]) if self.tree is None: self.tree = {} self.tree[parent_node] = {} for value in feature_values: subtable = self.split_table(self.train_data, parent_node, value) f_val, counts = np.unique(subtable[:, -1], return_counts=True) if len(counts) == 1: self.tree[self.features[parent_node]][value] = f_val[0] else: self.tree[self.features[parent_node]][value] = self.build_tree(subtable) return self.tree @staticmethod def split_table(table, node, value): table = table[table[:, node] == value] return np.delete(table, node, 1) if __name__ == '__main__': # Preparing Data train_data = pd.read_excel("/home/nakul/Documents/Train Data.xlsx") test_data = pd.read_excel("/home/nakul/Documents/Test Data.xlsx") # Converting data to numpy array since it is faster than pandas features = train_data.columns[:-1].values # Extracting all feature names train_data = train_data.values # Extracting data obj = Tree(train_data, features) tree = obj.build_tree(train_data) ``

2020.09.04 00:33 Yutyu Array Formula crashing applications, checked the compatibility of the file and seems like my formula is the cause

I could not understand what is wrong with my array formula, even though it opens/saves without an issue in Excel 365, when uploaded to my code running using Excel libraries like ClosedXML or EPPlus it always crashes when saved. I suspect it might be this array formula because when I remove the array formula, the code is able to perform the save just fine.

https://preview.redd.it/frjl8xyci0l51.png?width=1143&format=png&auto=webp&s=734132e70bafc3cea53178e56c800b2db948d00c
Here is the simplified array formula that I'm using
``=IF([@Month]=tomthyr,IF(OR([@Margin2]-[@Margin]>=0.1,[@Margin]-[@Margin2]>=0.1), "Yes", "No"),INDEX(SourceData[[#All],[Bleeding]],MATCH(1,(SourceData[[#All],[Month]]=tomthyr)*(SourceData[[#All],[Name]]=[@Name]),0))) ``
tomthyr refers to Cell B3
The formula check if month = Cell B3, then performs logic in column B and C and returns Yes/No. As a result, Row 17 has Dec-19 date, similar to Cell B3, so the calculation is applied which results in No, the other rows that doesn't match Cell B3 will have to reference the value of row that does (row 17) with the same company name (column A), making them all having identical values. Live data will have yearly data of over 50 companies. This is a simplified example with only 1 sample company.

The formula works fine but this implementation seems to be bad. What am I doing wrong here?

2020.09.03 11:33 nylefidal Using names to store formulas with relative referencing

 We know how important names can be; it can be used to store values to make our formulas easier to read, as well as, an easier way to call values. For example, in our sheet we need to multiply VAT to multiple cells. So instead of multiplying the cost of goods (e.g. 10usd), by let's say, 1.12 and maybe confusing yourself in the future as to what that number is, you can just input =10xVAT, where VAT is named cell which contains the value 1.12. There are other uses of Names which are explained in detail here. A few weeks ago, there was a post on excel about using autocorrect as an alternative to macros. There was one user there though who suggested about using Named Functions. The way this works is Named functions are like a simple UDF, allowing you to store formulas with relative referencing. For example, we want to use index-match multiple times. The pictures below should be able to explain the point of this post but I explained further below: ​ ​ ​ Table Name: Inventory ​ User Input is in Yellow ​ Original Formula ​ Simplified Formula Output is still the same In my case, I named the user input form as a named range so that whenever I need to search for something i can bring it up easily. In this case, let's name this 4x1 range `UserInput`. The function that the author used is `=INDEX(Inventory[Price],MATCH(1,(H5=Inventory[Item])*(H6=Inventory[Size])*(H7=Inventory[Color]),0))` which is inputted in the 4th row in `UserInput`. The fun thing about Named Function is that, the function above can be Named, like let's say `PriceSearch`. So, with the combination of `UserInput` and `PriceSearch`, this allows for me to search the price of whatever item I need with just 2 functions instead of pasting the index-match formula multiple times and copy pasting (or manually inputting) the user input form. Overall increased efficiency with excel. How did I do this? ​ Create New Name ​ Input function There are multiple formats on how you want to do your referencing. But the important detail is an exclamation mark (without the sheet name) in `=!A1` allows you to have relative referencing in *any* sheet. So, for example the table is formatted as a range and it is located in sheet3 but the calculations and output are in sheet1 (Let's assume Table(now range) is in `A2:D8` and UserInput is in `G1:G3` and price is in `G4`). So our formula in Price would then be `=INDEX(D2:D8,MATCH(1,(G1=A2:A8)*(G2=B2:B8)*(37=C2:C8),0))` and in Refers to would be `=INDEX(Sheet3!\$D\$2:\$D\$8,MATCH(1,(!G1=Sheet3!\$A\$2:\$A\$8)*(!G2=Sheet3!\$B\$2:\$B\$8)*(!G3=Sheet3!\$C\$2:\$C\$8),0))`. The Arrays used in Index and Match still have `Sheet3!` since our database shouldn't move location but the location our user inputs in is relative to the cell, thus, we only need to put `!` As the redditor in the linked post said, this is not an alternative to robust User Defined Functions, but for simpler functions, this should be a good alternative. :D submitted by nylefidal to u/nylefidal [link] [comments]

2020.09.03 05:41 monxstar I want to share a huge discovery: Using names to store formulas with relative referencing

We know how important names can be; it can be used to store values to make our formulas easier to read, as well as, an easier way to call values. For example, in our sheet we need to multiply VAT to multiple cells. So instead of multiplying the cost of goods (e.g. 10usd), by let's say, 1.12 and maybe confusing yourself in the future as to what that number is, you can just input =10xVAT, where VAT is named cell which contains the value 1.12. There are other uses of Names which are explained in detail here.
A few weeks ago, there was a post on excel about using autocorrect as an alternative to macros. There was one user there though who suggested about using Named Functions. The way this works is Named functions are like a simple UDF, allowing you to store formulas with relative referencing. For example, we want to use index-match multiple times. The pictures below should be able to explain the point of this post but I explained further below:
I named this table, Inventory

User Input is in Yellow

Original Formula

Simplified Formula

Output is still the same

In my case, I named the user input form as a named range so that whenever I need to search for something i can bring it up easily. In this case, let's name this 4x1 range `UserInput`. The function that the author used is `=INDEX(Inventory[Price],MATCH(1,(H5=Inventory[Item])*(H6=Inventory[Size])*(H7=Inventory[Color]),0))` which is inputted in the 4th row in `UserInput`. The fun thing about Named Function is that, the function above can be Named, like let's say `PriceSearch`. So, with the combination of `UserInput` and `PriceSearch`, this allows for me to search the price of whatever item I need with just 2 functions instead of pasting the index-match formula multiple times and copy pasting (or manually inputting) the user input form. Overall increased efficiency with excel.
How did I do this?
While I'm on the cell beside price, I click on the Name Manager and create New Name.

Instead of inputting a range or a cell in \"Refers to: \", I instead inputted the function
There are multiple formats on how you want to do your referencing. But the important detail is an exclamation mark (without the sheet name) in `=!A1` allows you to have relative referencing in *any* sheet. So, for example the table is formatted as a range and it is located in sheet3 but the calculations and output are in sheet1 (Let's assume Table(now range) is in `A2:D8` and UserInput is in `G1:G3` and price is in `G4`). So our formula in Price would then be `=INDEX(D2:D8,MATCH(1,(G1=A2:A8)*(G2=B2:B8)*(37=C2:C8),0))` and in Refers to would be `=INDEX(Sheet3!\$D\$2:\$D\$8,MATCH(1,(!G1=Sheet3!\$A\$2:\$A\$8)*(!G2=Sheet3!\$B\$2:\$B\$8)*(!G3=Sheet3!\$C\$2:\$C\$8),0))`. The Arrays used in Index and Match still have `Sheet3!` since our database shouldn't move location but the location our user inputs in is relative to the cell, thus, we only need to put `!`
As the redditor in the linked post said, this is not an alternative to robust User Defined Functions, but for simpler functions, this should be a good alternative. :D

2020.09.03 01:51 StormyMcManor Two recurring errors on Access to Excel VBA script

I have two recurring issues that I cannot figure out in this code: 1) If I don't have screenupdating on, the code runs fine. But when I turn it off at the start I get run-time error '91' on the last line of the code when turning screenupdating back on. 2) If I have already created the file, and overwrite it I get error '1004' on rostercount = Range("C3", Range("C3").End(xlDown)).Count. If I just end the code and rerun it, it runs fine. Literally no other changes other than just rerunning the code.
Any suggestions are greatly appreciated and thank you for your help.
Here is a copy of my code for reference. >! Dim appExcel As Excel.Application Dim myWorkbook As Excel.Workbook Dim rostercount As Integer Dim rostercount2 As Integer Dim i As Integer Dim j As Integer Dim roster() As String Dim roster2() As String Dim activity() As String Dim city() As String Dim city2() As String Dim companyname() As String Dim firstname() As String Dim lastname() As String Dim perdiem() As Integer Dim perdiem2() As Integer Dim clockin() As String Dim clockin2() As String Dim clockout() As String Dim clockout2() As String Dim hrswork() As Double Dim hrswork2() As Double Dim hrsbilled() As Double
``Set appExcel = CreateObject("Excel.Application") Set myWorkbook = appExcel.Workbooks.Open(strFileName) appExcel.Visible = True appExcel.ScreenUpdating = False appExcel.Range("E:E").Insert 'appExcel.ActiveSheet.Range("C3").Select rostercount = Range("C3", Range("C3").End(xlDown)).Count rostercount2 = rostercount * 2 ReDim roster2(rostercount) As String ReDim activity(rostercount) As String ReDim city(rostercount) As String ReDim companyname(rostercount) As String ReDim perdiem(rostercount) As Integer ReDim clockin(rostercount) As String ReDim clockout(rostercount) As String ReDim hrswork(rostercount) As Double ReDim hrsbilled(rostercount) As Double 'half the values that need to be halfed i = 1 For i = 1 To rostercount If appExcel.Cells(i + 2, 4).Value <> "" Then appExcel.Cells(i + 2, 11) = appExcel.Cells(i + 2, 11) / 2 If appExcel.Cells(i + 2, 7) <> 0 Then appExcel.Cells(i + 2, 7) = appExcel.Cells(i + 2, 7) / 2 End If End If Next i 'fill arrays i = 0 For i = 0 To rostercount - 1 roster2(i) = appExcel.Cells(i + 3, 4) activity(i) = appExcel.Cells(i + 3, 6) city(i) = appExcel.Cells(i + 3, 2) companyname(i) = appExcel.Cells(i + 3, 1) perdiem(i) = appExcel.Cells(i + 3, 7) clockin(i) = appExcel.Cells(i + 3, 8) clockout(i) = appExcel.Cells(i + 3, 9) hrswork(i) = appExcel.Cells(i + 3, 10) hrsbilled(i) = appExcel.Cells(i + 3, 11) Next i 'move person 2 data to bottom i = 0 j = 0 For i = 0 To rostercount - 1 If roster2(i) <> "" Then appExcel.Cells(rostercount + 3 + j, 1) = companyname(i) appExcel.Cells(rostercount + 3 + j, 2) = city(i) appExcel.Cells(rostercount + 3 + j, 3) = roster2(i) appExcel.Cells(rostercount + j + 3, 6) = activity(i) appExcel.Cells(rostercount + j + 3, 7) = perdiem(i) appExcel.Cells(rostercount + j + 3, 8) = clockin(i) appExcel.Cells(rostercount + j + 3, 9) = clockout(i) appExcel.Cells(rostercount + j + 3, 10) = hrswork(i) appExcel.Cells(rostercount + j + 3, 11) = hrsbilled(i) j = j + 1 End If Next i 'setup new arrays rostercount = appExcel.Range("C3", Range("C3").End(xlDown)).Count ReDim roster(rostercount) As String ReDim city2(rostercount) As String ReDim firstname(rostercount) As String ReDim lastname(rostercount) As String ReDim perdiem2(rostercount) As Integer ReDim clockin2(rostercount) As String ReDim clockout2(rostercount) As String ReDim hrswork2(rostercount) As Double i = 0 For i = 0 To rostercount - 1 roster(i) = appExcel.Cells(3 + i, 3) Next i 'seperate first and last name i = 0 For i = 0 To rostercount - 1 appExcel.Cells(3 + i, 4) = Left(roster(i), InStr(roster(i), " ") - 1) appExcel.Cells(3 + i, 5) = Right(roster(i), Len(roster(i)) - Len(appExcel.Cells(3 + i, 4)) - 1) appExcel.Cells(3 + i, 1) = "Company" 'fill new arrays firstname(i) = appExcel.Cells(3 + i, 4) lastname(i) = appExcel.Cells(3 + i, 5) city2(i) = appExcel.Cells(3 + i, 2) perdiem2(i) = appExcel.Cells(3 + i, 7) clockin2(i) = appExcel.Cells(3 + i, 8) clockout2(i) = appExcel.Cells(3 + i, 9) hrswork2(i) = appExcel.Cells(3 + i, 10) 'move data appExcel.Cells(3 + i, 2) = firstname(i) appExcel.Cells(3 + i, 3) = lastname(i) appExcel.Cells(3 + i, 4) = city2(i) appExcel.Cells(3 + i, 8) = clockin2(i) appExcel.Cells(3 + i, 9) = clockout2(i) appExcel.Cells(3 + i, 10) = hrswork2(i) appExcel.Cells(3 + i, 11) = perdiem2(i) Next i 'move formatting and remove cells no longer needed appExcel.Columns(5).EntireColumn.Delete appExcel.Columns(6).EntireColumn.Delete appExcel.Cells(3, 7).Copy Range("G3", Range("G3").End(xlDown)).PasteSpecial xlPasteFormats Range("F3", Range("F3").End(xlDown)).PasteSpecial xlPasteFormats appExcel.Range("F:F").Insert 'make the header i = 0 Cells(1, 1).Value = "Contractor" Cells(1, 1).Font.Bold = True Cells(1, 2).Value = "Crew Member First Name" Cells(1, 2).Font.Bold = True Cells(1, 3).Value = "Crew Member Last Name" Cells(1, 3).Font.Bold = True Cells(1, 4).Value = "Market-City" Cells(1, 4).Font.Bold = True Cells(1, 5).Value = "Project Duty Performed" Cells(1, 7).Value = "In" Cells(1, 8).Value = "Out" Cells(1, 9).Value = "Total Hours (does not include lunch)" Cells(1, 9).Font.Bold = True Cells(1, 10).Value = "Per Diem" Cells(1, 10).Font.Bold = True For i = 0 To 9 Cells(1, i + 1).VerticalAlignment = xlBottom Cells(1, i + 1).HorizontalAlignment = xlCenter Cells(1, i + 1).WrapText = True Cells(1, i + 1).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.249977111117893 .PatternTintAndShade = 0 End With With Selection.Font .Name = "Calibri" .Size = 12 .Color = vbBlack End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin End With Cells(2, i + 1).Clear Cells(2, i + 1).VerticalAlignment = xlBottom Cells(2, i + 1).HorizontalAlignment = xlCenter Cells(2, i + 1).Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorDark1 .TintAndShade = -0.249977111117893 .PatternTintAndShade = 0 End With With Selection.Font .Name = "Calibri" .Size = 12 .Bold = True .Color = vbBlack End With Next i appExcel.ErrorCheckingOptions.BackgroundChecking = False Range("A2", "H2").Merge appExcel.ErrorCheckingOptions.BackgroundChecking = True Cells(2, 1).Value = "Totals" Cells(2, 9).Value = appExcel.WorksheetFunction.Sum(Range("I3", Range("I3").End(xlDown))) Cells(2, 10).Value = appExcel.WorksheetFunction.Sum(Range("J3", Range("J3").End(xlDown))) Set appExcel = Nothing Set myWorkbook = Nothing Range("A3", Range("J3").End(xlDown)).Select With Selection.Font .Name = "Calibri" .Size = 11 .Color = vbBlack End With Range("G3", Range("J3").End(xlDown)).Select With Selection .HorizontalAlignment = xlCenter .WrapText = False .Borders.LineStyle = xlContinuous End With Range("A3", Range("E3").End(xlDown)).Select With Selection .HorizontalAlignment = xlLeft .WrapText = False .Borders.LineStyle = xlContinuous End With Rows("1:1").RowHeight = 78.75 Range("B3", Range("J3").End(xlDown)).RowHeight = 15 Columns("A:A").ColumnWidth = 12.14 Columns("B:B").ColumnWidth = 15.41 Columns("C:C").ColumnWidth = 14.71 Columns("D:D").ColumnWidth = 22.43 Columns("E:E").ColumnWidth = 29.43 Columns("F:F").ColumnWidth = 0.33 Columns("G:G").ColumnWidth = 10.86 Columns("H:H").ColumnWidth = 10.71 Columns("I:I").ColumnWidth = 11 Columns("J:J").ColumnWidth = 8.57 appExcel.ScreenUpdating = True ``
!<

2020.09.02 07:55 p8ntslinger The 4 Principles of Duck Hunting: Being the 1st Part of a Beginners Guide to Hunting for the SRA

The 4 Principles of Duck Hunting
This is not necessarily meant to be a step-by-step guide on how to start hunting waterfowl, but it generally applies and provides a foundation for beginning the journey by distilling the dizzying array of knowledge, facts, techniques, and skills into 4 major, overarching principles. I believe that this distillation, while dangerously hovering near oversimplification, will help new hunters to organize tips, thoughts, suggestions, and other things they learn from other sources into one of these 4 principles, hopefully solidifying seemingly independent ideas into a usable and relatable framework that is overall, helpful.
Skilled waterfowlers will no doubt have grievances with many details, but I think they will generally agree with the 4 principles and my presentation thereof as useful for new hunters in beginning the wonderful journey of waterfowl hunting. I’d love to discuss further refinement of this post in the comments.
In order of Importance:
Location
You can’t kill ducks sitting on your couch. You can’t kill ducks in a city park (not legally, anyway), so location is pretty important to duck hunting. In fact, it’s the most important part! If ducks aren’t within shotgun range, you’re not gonna kill any, so you need to do everything you can to be where ducks are.
This simple truth is not easy to act on and takes a considerable amount of time and effort to accomplish with any consistent success. Learning waterfowl biology, life history, and habits, then applying that knowledge in your search for birds is the key to choosing the right hunting spots. Waterfowl in North America are migratory and follow 4 major routes of migration, called flyways, every winter (southward migration) and spring (northward migration). The first is along the Pacific Coast Flyway(British Columbia, WA, OR, CA), the Central Flyway (Great Plains states from ND, MT to central TX), the Mississippi Flyway (states from the Great Lakes southward along the MS River), and the Atlantic Coast Flyway (ME down to FL). Waterfowl species fly south seeking warmer weather, food sources, and unfrozen habitat during the winter, then fly back north as spring comes, back to their nesting grounds in the marshes of Canada and the northern US. The closer you are or can get to the middle of each of these flyways, the denser and more numerous waterfowl numbers tend to get. For example, hunting 10 miles from the MS River in southern Missouri is going to be a better place than in the Appalachian mountains of eastern Tennessee, even though they are roughly on the same latitude.
Once you’ve chosen your flyway, the details begin to matter. Waterfowl like water, so water sources like rivers, streams, lakes, ponds, marshes, swamps, and other wetlands are going to be the most likely places to find ducks. They absolutely also frequent dry fields, especially agricultural fields that have waste seed laying on the ground as a food source, but as a general rule, waterfowl prefer water for habitat. Begin by looking up common duck food plants native to your region, emphasizing aquatic or moist-soil plants. Learn to identify them. Examples of aquatic plants are smartweed, millet, wild celery, pondgrass, etc. Moist soil plants like bottomland hardwood trees that produce edible seeds like oaks, others are also good. Look at areas where this food is present and abundant. Use USGS water data and weather reports to follow trends on rain, flooding, and water levels in local lakes, rivers, and wetlands throughout the months of your season. Make notes on what water levels need to be in order to hunt specific areas. For example- you can hunt wetland A when its water level is 12-48 inches, but its to hard to get there if the water is higher, and too dry when below 12 inches. Or, a river floods over its banks into a stand of trees when its water depth is above 12 feet, creating a new area for ducks. Focus on when water levels rise, since rising water floats food and draws more birds. Falling water often washes food away and ducks will leave.
Watch the weather north of you and pay attention to the freeze line and snow line. When water north of you starts freezing, that's a good sign you'll get a southward push of birds. When it starts snowing north of you and the ground get 4-6+ inches snow cover and it doesn't melt, that's enough to cover food in fields and you'll likely get a southward push. To find birds, go to dams, overlooks, boat ramps, or spots over waterways, fields, or other likely habitat where you can see a long way and search for groups of birds with binoculars. In the afternoon, look for groups landing-that's likely a roost. Try to track those birds in the morning to see where they go to feed. That's likely the best spot to hunt. Its likely that for every hunt you go on, you'll spend 2-3 days scouting. It's a grind. Don't be discouraged- keep at it and mark spots on GPS or maps, checking old spots regularly. Your effort will pay off. The larger your “library” of known, likely hunt locations is, the more options you will have to hunt when birds migrate into your area.
Once you have found a spot that looks promising, decide on how to set up. You need to be as close as possible to where ducks are landing, dependent on available cover and concealment. Try your best to set up with the wind at your back- ducks prefer to land into the wind when possible. Also, setting up with the sun not in your eyes makes for more comfort when hunting. Some places will be accessible on foot, others by vehicle, others by boat. Plan accordingly for this and it’s a good idea to go to a location the day before a hunt and map out an acceptable route to get in, so you don’t get lost in the pre-dawn twilight. Once you have arrived in your spot, its time to set up and begin thinking about…
Concealment
Concealment applies to the visibility of you, your hunting group, dog, boat, blind, and/or other unnatural features in your hunting spot. Ducks, like most birds, have excellent color vision and good depth perception. They see as well as people do or better. They see movement well and their survival depends on their ability to visually assess their surroundings for things that look wrong, like presence of predators, hazards, and other things that make a particular area undesirable. An area may have everything a duck needs to feel safe and secure, but a single negative element being present will likely dissuade birds from landing or utilizing that space.
The first and most obvious element of concealment is your person- wear appropriate colors and camo patterns for your surroundings. Do not wear bright colors, reflective or shiny elements, or high-contrast pieces of clothing. Cover your face- use a camo facemask or face paint, wear a hat, beanie, or other head covering.
Hide yourself amongst natural features- if vegetation is waist-high, try your best to not stick up significantly above it. Build blinds or hides using natural vegetation that occurs in the immediate vicinity of your location. If you’re hunting in a cattail marsh, use cattails not cornstalks. If you’re hunting in a cypress swamp, stand behind a cypress tree instead of building a grassy blind. I’ve used old refrigerator boxes to make blinds when hunting on barren mud flats and had success (same color brown as the mud!). When building blinds, cover your head in addition to front, sides, and back. Ducks fly in the air and they look down so your blind may be beautiful and invisible from the front, but if they fly over and see you sitting there surrounded by empty Cheeto bags and honey bun wrappers, they’re going to move on down the road.
Try to eliminate right angles, shadows, and heavy contrasts when building blinds. Use uneven spacing of elements to create natural-looking blobs that fit in with the features around you. On sunny days, utilize natural shadows to hide yourself (stand on the shaded side of a tree instead of the sunny side). If you hunt from a boat, make sure it is well-hidden, or at least stored a good distance from where you are hunting (at least 100 yards is a good rule of thumb). During the hunt, pick up your empty shotgun shells, make sure decoy cords are not conspicuously sticking out of the water. Often, when you have birds come in to look at your set up, they respond to calls, and work around you several times, but they do not commit, some element of concealment is likely your problem, and making the smallest details are properly taken care of can be the difference between getting skunked and shooting a limit. Now that you’ve achieved a level of invisibility that the Predator would envy, start setting up your decoys.
Motion
Ducks, being living critters, move around. They fly, swim, walk, splash, dive, jump, etc. Duck decoys do not do this because they are not alive. Find ways to create motion in your decoys and in the area of your decoys, be it water or dry ground. Motion helps to make your decoys visible from long distances, drawing in far birds who may not otherwise see them, and it also gives nearby birds more reason to be confident that your decoy spread is alive and that your spot appears safer and more secure. 1-2 dozen decoys is a good starting number for beginner hunters and buying cheap decoys or used decoys is a fine way to start. Make sure that your decoys are tethered in such a way that they will not float away. While scouting, hopefully you have observed how live ducks sit together on water or in fields- do your best to mimic this distribution. Its easy to overthink, try not to.
Creating artificial motion is most important on calm days, when wind isn’t there to do the job for you. A glassy water’s surface with deadstill decoys appears unnerving and the real birds will notice this. The same goes for hunting dry fields. The two simplest ways to do this are among the most effective AND cheapest.
One is to kick and splash the water using your feet, a boat paddle, a rocking boat, splashing a tree branch, etc, churning the water and creating ripples that propagate throughout your decoys, giving the appearance of ducks swimming and moving from a distance. This also kicks up sediment, a natural tell-tale sign that ducks are stirring up the bottom looking for food. Its best to do this when birds are not right on top of you, since it draws attention to your position, but its very effective when hunting over water.
The other excellent way to create motion in your decoys is to buy or preferably build a jerk string. This is a long piece of string with a bungee cord and anchor weight on one end, with several decoys attached, that you pull back and forth, imparting splashing ripples and swimming action to the decoys attached to the string. You can make 3-4 decoys seem like they are actively swimming, diving for food, and the result is very convincing.
There are all sorts of static and motion decoy designs available, DIY and storebought, cheap and very expensive. 2 of the most popular ones besides jerk strings are spinning wing decoys, which give the illusion if a duck flapping its wings to land, and silhouette decoys, which are 2D decoys (think duck shapes drawn on and painted on stiff boards or plastic sheets) arranged in a "Y" shape. When a real bird flies around it, only 2 of the 3 sides of the "Y" are visible at any given time, giving a twisting, shifting illusion that appears to be moving ducks. Almost all the fancier, electronic motion decoys for sale are relatively expensive, not necessarily legal to use everywhere, so I advise against them for beginners to buy. The jerk string is so effective and cheap that its perfect until you decide to make a sizable investment into waterfowl hunting.
Calling
The importance and value in calling ducks is somewhat overestimated by many people and duck hunters, much like the idea that you must have a retriever dog in order to hunt. You don’t need a dog, and while you should be able to call ducks or learn to do so, it is the least important of the essential elements of duck hunting. If you have scouted properly and are hunting where the ducks want to be, you are properly concealed, and you have some motion in your decoys, you can and likely will kill ducks without even bringing a call with you.
However, calling is still a powerful tool. At its highest level, it easily approaches high art in complexity and nuance. The best duck callers are not merely able to notify birds of their presence or able to attract ducks vocally, but are able to read duck behavior and actively communicate with live birds in a give and take 2-way relationship that is amazing to watch. It takes years to get to such a level and lots of practice and hunting. But, you can achieve good results with some pretty basic tools and techniques.
I recommend new hunters buy 3 duck calls- 2 standard mallard hen calls that have different tones and volume from each other (one soft and mellow, one loud, harsh, and/or raspy) and a 6-in-1 mallard drake whistle. Make or buy a call lanyard to keep your calls around your neck. There is no need to spend more than \$100 on all 3 calls combined. The reason why you should get mallard duck calls is that they are the most common species of duck in North America and almost all other duck species will respond to mallard calls. It is as close a universal call as there is for ducks. There are tons of brands, but Duck Commander, Haydel’s, Primos, and Buck Gardner all offer usable, but cheap calls that will work well for beginners.
The mallard hen calls have 3 basic sounds you should learn to make- a quack, a hail/comeback call, and a chuckle/feed call. The quack is the easiest and most fundamental vocalization and serves as a “hey I’m content and everything here is chill” type vocalization. The quack is made by pursing the lips, tightening your cheeks and blowing from your diaphragm, making a “VUT” or “VOOT” sounds with your tongue. The hail call is simply a 3 or 5 note string of quacks descending in volume but ascending in tempo- QUACK—Quack-quackquackquack. This call functions as “hey, I’m over here, please come join me” vocalization and is the main way of calling distant birds to you. The chuckle/feed call is made by again pursing the lips, tightening the mouth/cheeks, and blowing a fast “DUKDUKDUK…” or “TUKTUKTUKTUK…” sound into the call. Its not as loud, but mimics the sound of ducks who are happily feeding on tasty food. The whistle can be used to mimic the sounds of some other duck species like teal, pintail ,etc, but is mainly used to make the sound of a male mallard duck. To make the drake whistle, hum and lightly blow into the whistle to make a soft, buzzy note. This call can be very effective and while is not a primary method of attracting birds, adds a depth and color to your duck calling that helps to mimic multiple birds calling and vocalizing at one time.
Once you are reasonably capable of making these calls, you will begin to develop a personal technique, tone, and sound that is all your own. The art of calling ducks comes in learning duck behavior and reading how ducks respond to your calls as they fly. This can get complicated and is difficult to describe, but generally, ducks headed in your direction don’t need to be called as hard or as often as those who are passing by. To pull ducks from afar, a few loud quacks combined with a hail call will tell them that you are there and interested. When they fly closer to investigate and begin circling (working is the proper term) your position, vary your calls- when they are flying in front and turning towards, you, hit them with a few softer, lazy-feeling quacks and as they fly above you, a string of chuckles and a drake whistle serve to add variety and realism. When they turn away from you, then hit them with the hail/comeback to signify that you want them to, you guessed it, come back. There is a ton of nuance in this process and really the best way to learn is experience.
Becoming an effective duck hunter requires that you learn and refine techniques related to all 4 of these areas, in concert with each other. Waterfowl hunting can be very demanding and very few hunters are able to truly master it. The journey of learning how to duck hunt is very rewarding and allows for some truly life-changing experiences in nature. While I do not believe waterfowl hunting is necessarily a great place for people new to hunting to start, because of the large amount of gear necessary to be successful relative to other types of hunting, it is definitely one of the most popular types of hunting for a good reason- its an absolute blast and provides a lifetime of enjoyment.
Ducks Unlimited
Delta Waterfowl
US Fish and Wildlife Service Migratory Bird Program
Realtree Waterfowl Hunting
HP Outdoors Waterfowl Podcast
Dr. Duck Podcast

2020.09.01 00:07 Gregregious Variation between current and legacy (ctrl-shift-enter) array logic

Hello all, this isn't an Excel problem exactly, but something that came up while I was trying to help u/DMoogle that stumped me (here's the post). This issue is the first time in my long and illustrious career on this sub that I've run into something about the way Excel works that I just can't explain, but it's obscure enough that it really doesn't matter except as a curiosity.
In the post I mentioned, I wrote a complex array formula that provided the correct answer on 2020 Office 365. However the user is on 2016, and using the same formula - the only difference being they had to enter it with Ctrl-Shift-Enter - provided them with different results.
Here I've made a simplified example that recreates the issue:
https://imgur.com/Q3mePpa
The table at the top left is a lookup table for multipliers. The table beneath it contains entries tagged with categories which correspond to the appropriate multiplier (the blue numbers show the individual results). The idea is to write a formula that sums the calculations for each name, in this case by passing multiple values to an INDEX formula to create an array of lookup results.
You can see the issue. Simply entering the formula on the 2020 version gives the correct results, while doing a Ctrl-Shift-Enter does not. Here's where it gets interesting:
https://imgur.com/16pIAP1
I've evaluated the formula in H2 all the way the penultimate step, and it looks correct. One click later:
https://imgur.com/WXq9gcz
How the hell does that happen?
Now I know this is the sort of problem that could be easily solved by a helper column, but I'm really curious to know what causes this. With my experimentation, it only seems to happen in formulas which aggregate arrays generated by the INDEX function. Specifically, it treats those arrays as if only the first value exists.
Here's a link in case anyone is interested.

2020.08.31 17:46 DMoogle How to SUM an array of lookups?

I'm trying to make a function that can lookup multiple values and SUM the result. This is a follow-up from my previous post of a more complex question here.
Basically I'm trying to do something like:
``SUM(INDEX(data_table,{4,2},{3,1})) ``
With the INDEX formula returning two values in the locations {4,3} and {2,1}, then the SUM formula summing them. However, this formula doesn't work (even entered as an array formula) - is there an alternative that'll do what I need? I tried a variation with SUMPRODUCT, but that didn't work either. Open to any suggestions.

2020.08.29 08:35 KagsTheOneAndOnly [OC] LeBron vs Kawhi player analysis (written in July)

So I had this post written out about a month back (ie before the restart happened) but never really got the chance to post it, so here it is if anyone's interested in giving it a read!
LeBron's profile is in the main body of the post and Kawhi's profile will be in the comments (length issues).

# Before we begin...

This post pilfers/references numerous ideas from the excellent Thinking Basketball YouTube channel, run by Ben Taylor. I highly recommend you also watch these highly informative, well-made, and entertaining player breakdowns:

# Definitions and Terms I'll be using

FULL LIST + EXPLANATIONS ON IMGUR: per 75, TS%, rTS%, ORTG, rORTG, PnR, PPP, AST%, Backpicks Passer Rating, Box Creation, PIPM, BPM, RAPTOR, RPM.

# LeBron James "LeBron", "LBJ", "King James"

In a nutshell: 35 y/o, Los Angeles Lakers PG/SF, 6-9, 250lb, Strong MVP Candidate. Stats: 25.7/7.9/10.6/1.2/0.5 with 4.0 TOVs on 49.8/34.9/69.7 shooting splits (58.2 TS%), 60 games played. Nerd stats: 31.6% Usage, +10.8 On/Off, +6.1 PIPM (3rd) , +7.7 RAPTOR (4th) , +7.4 BPM (Backpicks) (2nd) , +8.7 BPM (BBRef) (4th) , +9.80 RPM (ESPN) (2nd) .

## The good:

• 🟢 Elite rim-centric scorer (26.3 points per 75 on +1.8 rTS%)
LeBron had a slow start to the season efficiency-wise but picked up steam as he went along, eventually shaking off his rust fully and averaging a more typically LeBron-esque 26.8ppg on 60.8 TS% (+4.4 rTS%, 52/36/72 splits) in his final 25 games before the NBA suspended its season. LeBron ISOs regularly but with variable success (4th in frequency, but 'only' 0.92 PPP, 61st percentile), often setting up shop beyond the 3-point arc, either firing a pull-up 3 (4.0 pullup 3PA/G, 33 3P% on pullup 3s) or faking and blowing past his initial defender to try to score, draw a foul (5.9 FTA, 70 FT%), or punish the help defense with a pass. He's excellent at working in the PnR (86th percentile), running potent two-man games with the Lakers' bevy of bigs to obtain clean finishes at the rim.
The success of most LeBron-centric offenses generally starts and ends with his elite finishing at the rim, where he's still destructive - 43% of his total attempts come in the restricted area, where he's scoring at an elite 69 FG% rate. LeBron explodes past opponents with his burst (his first step remains pretty quick) and muscles through heavy contact to finish with an array of highly efficient layups (64 FG%) , finger rolls (62 FG%) , and dunks. Defenders reaching in to slow down his drives afford LeBron a decent foul-drawing rate (6.6 FTA, 67.9ft%). (Interestingly, this is actually the lowest free-throw-rate (FTr), 0.292, of his career.)
• 🟢 Not just a slasher
Nowadays, LBJ also often bullies smaller defenders in the post or tries to use his speed/agility against larger defenders, finding his way to the rim or uncorking a midrange fadeaway (37 FG%) - he's slightly slower and more ground-based but craftier than his younger days, grading out as an excellent post scorer overall (1.00 PPP, 78th percentile). Per Pete Zayas of the Athletic, "the Lakers as a team have scored 1.16 points per possession while shooting a stunning 63.1 effective field-goal percentage on LeBron post-ups, making it one of the most efficient, high-volume plays in the NBA".
2020 LeBron James is also taking more threes than ever before (career-high 6.4 3PA/G), a useful adjustment considering his slightly-slowed foot-speed/athleticism at age 35, and he's making them at a respectable rate (35 3P% overall, 93rd percentile in total 3PT gravity per BBall Index).
In terms of off-ball scoring, nearly a third (~30%) of his made baskets are assisted, a solid figure for a high-volume on-ball creator. He's a competent catch-and-shoot 3P-shooter (38 3p% on 2.1 catch-and-shoot 3s/game), and a decent cutter when the opportunity presents itself (though the Lakers' dearth of supplemental playmaking limits this aspect of his game).
LeBron leads the league in assists/assist-% (10.6 APG, 49.7 AST%, excellent 2.65:1 AST/TO ratio) and Passer Rating (9.5), and ranks 3rd in Box Creation (15.1). The Lakers have a strong offense (+2.6 rORTG) , which improves by a remarkable +5.7 points with LeBron on the floor. Notably, the team has the 4th-best offense in the league despite having (1) below-average spacing and (2) limited ball-handling.
As Mike Prada of SB Nation wrote in his excellent piece on James's playmaking, "The Lakers lack a modern NBA offense, and it doesn’t matter because LeBron’s passing is that spectacular ... LeBron both sees openings others can’t and he sneaks those passes into tighter windows than even other elite playmakers can". The Lakers' compact spacing demands surgical precision from their veteran playmaker: see here, here, here, and here.
In Year 17, there are few passes LeBron can't make - LeBron's passing is omnidirectional, comfortable diming up teammates from any angle and position on the court. James has always been a connoisseur of the skip-pass to weakside shooters, and he's a drive-and-kick maestro. In addition, he frequently connects with the Lakers' triumvirate of athletic bigs (Davis, McGee, Howard): LeBron flicks high-speed bounce-passses through masses of defenders, uncorks nifty interior drop-offs after collapsing the defense, and feathers in soft lobs. In the post, he can hit cutters or shooters, mimicking Jokic; in transition, he throws countless pinpoint outlet-passes too. LBJ also makes plenty of extra-passes -- the Lakers' team-assist-% rises by a not-insignificant +2.5 %-points with him on the court.
• 🟢 Chemistry with Anthony Davis
Borrowing this quote from my "Unicorns" post,
Vertical spacer: AD is arguably the GOAT lob-finisher (75 FG% from 0-3 feet). Davis's catch-radius is one of the best in NBA history. Just throw it up in the general direction of the rim and he'll make it work somehow with his touch and athleticism. His addition to the Lakers is a major reason why LeBron's leading the league in assists (2.8 of LeBron's 10.6 assists/game go to AD).
Of all 2-man assist-combos in the league this year, James to Davis ranks 1st by a vast margin.
LeBron hits AD in a variety of ways: in the pick-and-roll, with lobs and snappy interior dishes; pick-and-pop or drive-and-kick, with AD positioned in the midrange or out on the 3-point line; simply dumping it to AD in the post and letting him go to work; hitting AD quickly and accurately in transition as part of a new-age "Showtime": in semi-transition, or off made-field goals, LeBron lets AD leak out early in the shot clock to establish good post position quickly, and then hits him with a long-range outlet pass so that AD can ISO against an unprepared defense. The Lakers often get 1-2 buckets per game in this fashion alone.
• 🟢 Transition terror
LeBron's transition scoring needs no introduction - he's likely the most prolific transition scorer ever and transition opportunities add up to about 20% of his total made baskets this season.
However, if you've watched Lakers' games, you may notice that he hasn't been pushing the pace in transition quite as often as previous seasons. Instead, he's also been an excellent and extremely willing transition passer this year. Despite having a much older roster than last season, the Lakers have been more effective in transition this year (5th in transition efficiency, up from 19th in 2019) , thanks in large part to LeBron either leading the break himself/leaking out to catch a pass, or, increasingly often, rewarding other Lakers who leak out with inch-perfect long-range dimes of his own.
• 🟢 Playoff LeBron
LBJ has shown a noticeable jump in his production going from the regular season to the playoffs during his last few seasons in Cleveland (Thinking Basketball screencap). 2 years ago in his 9th Finals run, he averaged 34/9/9 on +6.4 rTS% (61.9ts%).
It maybe remains to be seen whether he can still access this heightened "playoff mode" going into the 2020 postseason at age 35, after going hard all regular season to lead the Lakers to the no. 1 seed. However, following the quarantine, he's well-rested, seemingly in excellent shape, and has more than earned the benefit of the doubt at this point in his career.
• 🟢 Good man defender, excellent team defender
LeBron James has started trying on defense again! He helped DPOY-candidate Anthony Davis captain a stingy 3rd-ranked Lakers defense (excellent -4.8 rDRTG; D improves by +5.0 points with LeBron on the court) - they had a defensive rating of 104.8 when James was on the floor, which would rank 2nd in the NBA. As writer Mo Dakhil observed, "he even took another step in his last 9 games before the NBA was suspended, with a rating of 99.4 when he was on the floor."
LeBron communicates constantly with teammates and often makes timely rotations, helping the helper and cleaning up mistakes. He still has good hands and excellent anticipation, jumping passing lanes, making astute double-teams in the paint, and he's strong enough to strip 290-pound Zion Williamson in transition.
LeBron uses his strength in the post (allows 0.63 PPP defending post-ups, 93rd percentile) : here he is harrassing Zion again, while poor Lauri Markkanen is out of his depth trying to back down LBJ. James is a cerebral defender, familiar with opponent team plays, and he's also highly aware of opponent shooting-%s, infamous for sagging off poor shooters to rest or roam defensively elsewhere.
LeBron is quite switchable, guarding positions 2-4 at least 22% of the time each, spending the remaining ~20% of his possessions on PGs and Cs. Overall, with LeBron as the closest defender, players shoot a solid ~2% worse overall. His rebounding is also excellent (6.9DRB/Game) which adds defensive value, often out-muscling bigs to secure rebounds - team DRB% improves by +4.0% with LeBron on the court.
LeBron has lost some of that highlight-reel athleticism and timing that he harnessed to protect the rim during his All-Defensive years, but he still provides paint protection with his lightning-quick reactions and elite positioning (top 30 in league in charges drawn), size (6-9 without shoes, 250lb), strength, and good hands. He does well to provide vertical challenges on rim finishes without fouling, and doesn't chase blocks, rarely falling for pumpfakes.
Defensive metrics (take these with a few grains of salt) think quite highly of his defense: 3rd in DRAPM, 3rd in DRPM, 8th in DWS, 13th in DBPM(BBRef), 24th in LA-DRAPM, 34th in DPIPM, 74th in D-RAPTOR.
• 🟢 Durable
Aside from last season's freak groin injury, LeBron has rightfully earned an Ironman reputation throughout the course of his 17-year career. He's only missed 3 games this season. In addition to his regular season-availability, LeBron often ratchets up his minutes into the high 30s/ low 40s in the playoffs.

# The not-as-good:

• 🔴 Father Time is catching up to the King
Slowed foot-speed on both ends of the court:
Offensively, his first step isn't as explosive as it has been previously, and he can get caught or trapped by the paint defense more often, forcing up tough inside shots as a result. His offensive-style is more ground-based in general now, attacking the rim slightly less and upping his 3-point attempts (and jumpshots in general) with each passing season. It's possible this changes in the playoffs, though.
Defensively, his (Roy Hibbert voice) verticality when defending the paint is limited nowadays, which restricts his rim-protection somewhat. His signature chase-down blocks are rare now (0.6 blks/game).
Durability/healing - LeBron had the first serious injury of his career last season at age 34, and he'll likely experience longer healing times at his age if he receives any future injuries.
• 🔴 Defensive engagement (sometimes) / some defensive issues
While this is easily the most locked-in he's been on defense since his Miami tenure ended, LeBron still conserves energy on D occasionally, slow at rotating or closing out to shooters at times. His awareness can be an issue (loses track of cutters every now and then), and he can be guilty of over-helping, sagging off mediocre shooters to a sometimes unhealthy degree. He also strikes out on steal attempts every so often, which bleeds defensive value. The Lakers also let LeBron spend most of his time guarding 'weaker' offensive players, rarely taking on point-of-attack duties unless necessary, which likely brings his overall impact down a notch from All-Defensive level. This tactic does unleash him as a help defendesweeper, though, which he's excellent at, plugging up holes in the Lakers D with expert ease.
• 🔴 Minor passing flaws
Occasionally misses finding cutters, sometimes whiffs on seemingly wide-open passes. His delivery of passes to shooters can be off at times, not always hitting the shooting pocket perfectly.
• 🔴 Free-throw shooting
69.7FT%, this has been a persistent issue for LeBron ever since leaving Miami (70.3ft% in last 6 seasons, 67.9ft% as a Laker).
• 🔴 Some areas of scoring
Keep in mind that his elite rim-finishing gives him a wide margin for error in the other aspects of his scoring. He's the leading playoff scorer in NBA history. That being said...
◉ LeBron is mediocre from the midrange (35.9fg%), where he takes 15% of his shot attempts: he was the 10th-least-efficient midrange scorer in the league at the time the NBA suspended its season. While he's had occasional success in the past (e.g. the 2018 playoffs, when he shot a ridiculous 49fg% in the midrange on 121 attempts; shoutout LeBronto), he's generally inconsistent from here and hasn't historically been a great midrange scorer. It certainly adds potency to his scoring versatility, however -- it's been a staple of his arsenal since he's entered the league. The same goes for his scoring in the non-restricted area of the paint, slightly further out from the rim (aka 'floater range'), where he's "only" shot 37fg% this year(113 attempts).
◉ Recently, LeBron has also shown a tendency to settle for pullup-3s, and while he can certainly make these shots, he isn't great at them overall -- James shoots 33 pullup-3p%, which is decent and lands in the 58th percentile league-wide but at 49.4 eFG%, it likely still isn't as efficient as his post-scoring or drives as a consistent option on offense. However, he might attack the rim more in the postseason, assuming that these long 3s have been a conservation of energy play during the doldrums of the regular season.
◉ LeBron's clutch scoring hasn't been great this season, either (42ts%). He's been settling for jumpers often - nearly half (48.3%) of his clutch shots have been pull-up 3s, which, as we've just mentioned, aren't necessarily an efficient scoring option for him. However, the Lakers as a whole are an excellent clutch unit, indicating that the team's defense and LeBron's playmaking still make them a highly thorny opponent late in the 4th. Encouragingly, he was a veritable clutch-god in his famous 2018 Finals run, attacking the rim mercilessly to the tune of 62.7 clutch-ts%.

2020.08.23 00:52 Baricuda Struggling to find specific values within a 2D Array for Gearing Ratios

Hello Everyone! This is my first Post here so I hope I don't violate any rules, however I am at my wits end in finding a solution to to my problem. Now to preface this, I'm not overly experienced in MS Excel, nor have I received any formal training in it, so I apologize if the solution is apparent.
Enough of my preamble. Basically I am attempting to make a tool for determining the correct compound gear ratios to use to achieve an extremely accurate final gearing ratio. If you're unfamiliar with compound gears they are basically 2 gears of different sizes stuck together and form a ratio that can step up of step down the speed. I am using these ratios to refine the speed of the gears into my desired ratio.
Now, I've set the number of teeth of each gear to be between N=10 and N=50 and created what basically amounts to a multiplication table; each gear having it's own axis. This yields exactly 1600 individual values. These ratios, or modifiers as I prefer to call them can be used to modify and refine the Rpm going into the gear train to a desired value. However I don't feel like it is accurate enough to replicate the rotation of celestial bodies without the error rate compounding too noticeably per rotation.
What I Intend to do is use a series of "coarsing" (simple ratios like 1:5) compound gears to bring the RPM closer to the final value and have two "fining" (the ratios from the table) compound gears to bring it as close to the final Rpm as I can. To achieve this I have sorted the 40x40 array in order of ascending and created a new multiplication table using those modifiers. This is where my issue starts to come into play as the table of ratios would become 1600 by 1600 or ~2.5 million unique values. I would need to be able to find the closest match to the modifier that refines the RPM from the "coarsing" compound gears to the final Rpm
Ideally the function(s) I use would be able to spit out all the information I would need without the hassle of scrolling through massive tables; the closest Value, and numbers of teeth on each compound gear.
So far I've found that to find the closest value to the desired is by using MIN(ABS('array' - 'desired modifier') and it works on the 40x40 table at least, however from simple tests INDEX and MATCH do not. I'm sure it's because it's a 2D Array that I am searching up and those are not designed to handle it. I've looked into thew numerous LOOKUP's and unfortunate none of them have worked with finding the closest value to my desired one.
To really drive home the my plan:
Input: Desired Modifier (Exact value needed)
Output: Closest value to desired modifier & the numbers of teeth on each "fining" compound gears (two values of N for each compound gear, four in total)
I am not sure how easy it is to do what I am trying to do, though I feel like it is entirely within Excel's realm of possibility. So what do you think? am I on the right track or do you see any better ways to achieve the same result? I greatly appreciate any feedback or ideas you guys have. Thanks All!