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 identikit » Thu Jul 12, 2018 6:01 am

Geoff wrote:What do you mean by the Glatten bit, I'm not sure if my thing works with accents, let me know if it does.

when I open your file in my Excel, I automatically see your commands in German - TRIM, LEN etc. - trim - glätten. I want to hug you right now


Image
User avatar
identikit
 
Posts: 3965
Joined: Sat Jul 16, 2016 12:38 pm

Postby Geoff » Thu Jul 12, 2018 6:20 am

identikit wrote:
Geoff wrote:What do you mean by the Glatten bit, I'm not sure if my thing works with accents, let me know if it does.

when I open your file in my Excel, I automatically see your commands in German - TRIM, LEN etc. - trim - glätten. I want to hug you right now


Image


haha wasn't even aware there were German specific formula names, you learn something new every day!
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Thu Jul 12, 2018 6:22 am

i guess you'll probably want to update the formula to just give the cell if it can't find any "[",

For that just wrap up the formula in an IFERROR as follows =IFERROR(TRIM(RIGHT(A2,LEN(A2)-FIND("]",A2,1))),A2)
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby identikit » Thu Jul 12, 2018 6:27 am

lol thank you, I indeed want that. my test glossary looks like a bomb hit it right now, but I am having a lot of fun!

I want to learn formulas now, I don't know why I keep treating Excel like potentially highly toxic materal - don't touch it too much, and then run! it's not gonna bite, is it.
User avatar
identikit
 
Posts: 3965
Joined: Sat Jul 16, 2016 12:38 pm

Postby Geoff » Thu Jul 12, 2018 7:33 am

glad to be of service, excel is really super easy.
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Thu Jul 12, 2018 5:03 pm

Woah just found this thread I'm gonna post some questions later
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Thu Jul 12, 2018 5:13 pm

Two burning ones are:

Can I make custom keyboard shortcuts on 365?

And whats the best resource iyo for learning new excel things, other than exploratory trial and error. This also applies to the rest of office suite
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Thu Jul 12, 2018 5:33 pm

Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby Robert » Thu Jul 12, 2018 5:37 pm

farmer wrote:Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?


Dunno about Excel, but in Word you can go to Review -> Compare.
User avatar
Robert
 
Posts: 3169
Joined: Sat Aug 21, 2010 11:41 pm

Postby farmer » Thu Jul 12, 2018 6:17 pm

Robert wrote:
farmer wrote:Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?


Dunno about Excel, but in Word you can go to Review -> Compare.


Hmm yeah that looks like it does an OK job at what I'm looking to do, ty.

I hate the aesthetic of the track changes/review stuff in word
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby Jsn » Thu Jul 12, 2018 11:36 pm

Geoff. Any tips for backtesting algorithms? I always find it to be a drag.
User avatar
Jsn
 
Posts: 462
Joined: Thu Dec 07, 2017 11:15 pm
Location: Sherman Oaks

Postby lights » Thu Jul 12, 2018 11:45 pm

farmer wrote:Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?


I used to use a piece of software called Beyond Compare for this kind of thing. You select two files and it can omit everything's the same and only show the lines that are different. It was a little dicey when trying to compare PDFs, but it can compare most other file types really efficiently.

Note I have no idea on cost, but pretty sure there's a trial version you can play with.
User avatar
lights
 
Posts: 2749
Joined: Mon Dec 14, 2009 4:09 pm
Location: DC

Postby Geoff » Fri Jul 13, 2018 4:47 am

farmer wrote:Two burning ones are:

Can I make custom keyboard shortcuts on 365?

And whats the best resource iyo for learning new excel things, other than exploratory trial and error. This also applies to the rest of office suite


are you using a mac or pc farmer?
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Jul 13, 2018 10:28 am

Oh im pc af. Mac and office don't play nicely. I'm on 365, both personal and at work. Tons of excel work every day, wanna get better at it. Also w10 at home, w8. 1 at worj
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Jul 13, 2018 10:52 am

Jsn wrote:Geoff. Any tips for backtesting algorithms? I always find it to be a drag.


What are you trying to do examplewise
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby Jsn » Sat Jul 14, 2018 7:39 pm

Testing forecasting accuracy- by going back in time and running the algorithm with old data
User avatar
Jsn
 
Posts: 462
Joined: Thu Dec 07, 2017 11:15 pm
Location: Sherman Oaks

Postby draw » Sat Jul 14, 2018 11:00 pm

Just learn some python my dude
User avatar
draw
 
Posts: 2001
Joined: Fri Oct 20, 2017 1:49 pm

Postby Jsn » Sun Jul 15, 2018 3:41 am

Ok
User avatar
Jsn
 
Posts: 462
Joined: Thu Dec 07, 2017 11:15 pm
Location: Sherman Oaks

Postby Geoff » Sun Jul 15, 2018 4:24 am

draw wrote:Just learn some python my dude


that might be the more efficient way, but I'm sure you could do it in excel, or perphaps use KNIME if you want to backtest without knowing programming.
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby Grey Poupon » Sun Jul 15, 2018 5:09 am

I also do forecasting and would like to compare months vs older months or whatever in some kind of organised fashion

anyone got tips on where to start with python? or is this vbcode? is vbcode worth learning? where do I start?

sorry for the stupid questions, I'm just wondering if people have some practical advice, otherwise I'll just hit google
My listening experience in balanced mode reveals the great depth of EARTH
User avatar
Grey Poupon
 
Posts: 17246
Joined: Tue Dec 15, 2009 12:53 am
Location: phonied up

Postby farmer » Sun Jul 15, 2018 12:51 pm

Forecasting is a highly complex beast where you need to know a bunch of stats before you can get a sense of how accurate you were, but excel should be pretty good at "here's what we would have forecasted if we had used data set x vs data set y", depending on how you set up your model to incorporate data inputs in the first place
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Sun Jul 15, 2018 6:54 pm

farmer wrote:Two burning ones are:

Can I make custom keyboard shortcuts on 365?

And whats the best resource iyo for learning new excel things, other than exploratory trial and error. This also applies to the rest of office suite


what kind of custom shortcuts do you need, there might be on already, I suggest you read through this website that has a comprehensive list of excel shortcuts (222 of them!).

Otherwise check out this; https://www.laptopmag.com/articles/how- ... -shortcuts

Resource wise, I like the following websites;

https://chandoo.org/wp/
http://www.cpearson.com/Excel/topic.aspx
http://www.contextures.com/tiptech.html
https://www.mrexcel.com/forum/index.php
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Sun Jul 15, 2018 6:56 pm

farmer wrote:Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?


you could potentially apply some conditional formatting, are they in different files or different tabs?
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 03, 2018 10:21 am

Geoff wrote:
farmer wrote:Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?


you could potentially apply some conditional formatting, are they in different files or different tabs?


typically they are about 20 tabs in different files. basically, i'm often updating a project with data from the new year, and when im done with the project i'd like to be able to compare the differences quickly, not for analysis but for error-checking on my part.
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Aug 03, 2018 10:23 am

geoff, how much access experience do you have? i'm trying to figure out how to add a guide or comments to the database i'm building in case i forget what things are doing or someone else has to pick up where i left off, but i can't figure out how to add comments in access. even creating a dummy query and commenting out a section of sql doesn't work because comments don't work at all in access sql.
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 03, 2018 10:52 am

farmer wrote:geoff, how much access experience do you have? i'm trying to figure out how to add a guide or comments to the database i'm building in case i forget what things are doing or someone else has to pick up where i left off, but i can't figure out how to add comments in access. even creating a dummy query and commenting out a section of sql doesn't work because comments don't work at all in access sql.


Have not used access for well over a decade, but yeah you can't comment, but you do this

Select
...
From
...
Where
....
And "Comment: FYI, Access doesn't support normal comments!"<>""

Just use a Where function to add a comment, it's a useless way to do it though.
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 03, 2018 11:13 am

lol yeah i can see how that would work. thanks!

and what db software do you use instead of access?
farmer
 
Posts: 9943
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 03, 2018 11:38 am

I mostly use KNIME to access mysql databases
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Fri Aug 03, 2018 11:41 am

But I work for a tiny startup so not practical on scale
User avatar
Geoff
i like jazz
 
Posts: 5684
Joined: Sat Dec 26, 2009 8:33 am

Postby jewels » Fri Aug 03, 2018 11:47 am

Is it possible to group text and scale uniformly? The option to group is grayed out.
gold and glass wrote:When you get to heaven, do you get to see a list of which gimmicks belonged to who?
User avatar
jewels
 
Posts: 21245
Joined: Mon Dec 14, 2009 3:33 pm

PreviousNext

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

Who is online

Users browsing this forum: a is jump, Autarch, becky, brentwurst, Cone, deebster, flimsy, Frank, gandhi, Grumby, harmonica, hbb, hit record, Hugh, husbands, inmate, jewels, Kaputt, mynamerocks, neely o'hara, noiseandpop, number none, odilon redon, Organic Croutons, pablito, pocket shepherd, Poptone, rex, sevenarts, son of chucky, trouble, wuk