Ask Geoff About Excel (or Google Sheets)

Health insurance rip off lying FDA big bankers buying
Fake computer crashes dining
Cloning while they're multiplying
Fashion shoots with Beck and Hanson
Courtney Love, and Marilyn Manson
You're all fakes
Run to your mansions
Come around
We'll kick your ass in

Postby farmer » Fri Aug 10, 2018 3:08 pm

farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 3:21 pm

if you strip out the first bit
and just start with the index it works, but will throw up false positives in case were the start date exceeds any of those that are in the mapping
INDEX(C:C,AGGREGATE(15,6,ROW($1:$11)/(A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2),1))
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 3:29 pm

ok great ill test that out. i would really like a method of detecting times when the date being checked actually falls outside of any of the acceptable ranges, though. in some ways that's the most important part, because it means there's errors in my table that need to be addressed.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 3:31 pm

of course, i need to fix it that the blank initial bit was supposed to blank it out if it was outside any defined range, but it didn't work properly i need to rethink to solve it.
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Fri Aug 10, 2018 3:35 pm

ok found a fix, do you want it to show no match or blank for dates out of range of first table?
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 3:37 pm

i mean i'd be fine with it throwing up YOUFUCKINGMORON because it means i need to fix things.

for context, table 1 is a list of insureds and the dates their policies are active, and table 2 is the current list of claims/losses, so if someone's made a claim outside of their policy period there's an error elsewhere in my work that needs fixin'.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Aug 10, 2018 3:38 pm

though it's more likely that the error was made by someone else that preceded me on this project and they just didn't have the wherewithal to try to diagnose their errors and here we are.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 3:42 pm

=IF(SUMPRODUCT((A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2))=0,"OUCH",INDEX(B:B,AGGREGATE(15,6,ROW($1:$11)/(A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2),1)))

New and improved formula :)

http://s000.tinyupload.com/?file_id=873 ... 4325751751

I just said OUCH (for no match), couldn't call you that thing you suggested
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 3:43 pm

oh yeah i custom-insult myself all the time in error checking sheets, but it's prob not good practice in case i hand the sheet to someone else who doesnt know about my little jokes.

and thanks! lets see if this works.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Aug 10, 2018 3:44 pm

as an aside to learn more about excel through this, what exactly is "A$1:A$11=E2" actually doing? also, what's the aggregate function doing? never used it, but it seems like it could be useful.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 3:47 pm

"A$1:A$11=E2" it's matching the name, in addition to the dates.
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 3:54 pm

how does it differ from the match() function, except that its a boolean return rather than a row number return
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 4:43 pm

farmer wrote:how does it differ from the match() function, except that its a boolean return rather than a row number return


It's mostly for use in array of other checks and yes for this example needed to convert stuff to boolean output
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 4:47 pm

oh sick. okay yeah this is the sort of tips and tricks i need. lesser known functionality that i can combine with others to solve problems i encounter. basically my goal is to eventually get my sheets to where my own tendency toward error is near zero.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Aug 10, 2018 4:50 pm

here's a completely unrelated general question about external references (other workbooks that require a filepath as part of the formula if you don't also have it open).

without using vba (ie entirely in excel), is there a way to concatenate things together to call a list of filepaths. like, a particular project will have folders by year, and close to identical file structure within those folders, so i really don't need to even open the file to pull what i need, and i can manually adjust file paths in formulae to pull the numbers i need from a different workbook, but i can't figure out how to use some form of concatenation/indirect, etc to basically have a series of cells that get combined into the file path, that is then used automatically to go pull numbers from a workbook, one year forward. ie i just wanna change a 2017 to a 2018 and have all the numbers i need updated.
farmer
 
Posts: 9827
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 11:40 pm

farmer wrote:here's a completely unrelated general question about external references (other workbooks that require a filepath as part of the formula if you don't also have it open).

without using vba (ie entirely in excel), is there a way to concatenate things together to call a list of filepaths. like, a particular project will have folders by year, and close to identical file structure within those folders, so i really don't need to even open the file to pull what i need, and i can manually adjust file paths in formulae to pull the numbers i need from a different workbook, but i can't figure out how to use some form of concatenation/indirect, etc to basically have a series of cells that get combined into the file path, that is then used automatically to go pull numbers from a workbook, one year forward. ie i just wanna change a 2017 to a 2018 and have all the numbers i need updated.


excel is terrible for that, and to use an indirect formula to look at alternative workbooks you'd need to open them first for the indirect to work.
User avatar
Geoff
i like jazz
 
Posts: 5458
Joined: Sat Dec 26, 2009 8:33 am

Previous

Return to Mamma Mia... Here We Go Again....

Who is online

Users browsing this forum: 10cc, Bad Craziness, beefbroth inthe shitpipes, doublethink0, futurist, gershon, Google Adsense [Bot], grammatron, Grey Poupon, Hauntedattic, hired goon, jalapeño ranch, Link, loaf angel, lunatic96, mactheo, Milquetoaster Strudels, palmer eldritch, patrick cokane?, Plainsong, scrumptown, silverapples, Sobieski, subtitles, That Demon Life, trigross, trouble, tyler_c, unsandpiper, worrywort