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: 3943
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: 5231
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: 5231
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: 3943
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: 5231
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: 9706
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: 9706
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: 9706
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: 3092
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: 9706
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: 449
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: 2720
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: 5231
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: 9706
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: 9706
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: 449
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: 1806
Joined: Fri Oct 20, 2017 1:49 pm

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

Ok
User avatar
Jsn
 
Posts: 449
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: 5231
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: 16657
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: 9706
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: 5231
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: 5231
Joined: Sat Dec 26, 2009 8:33 am

Previous

Return to Don't. Let. Go. You’ve got the music in you.

Who is online

Users browsing this forum: acapelli, ahungbunny, ASH NERVE, Bad craziness, Bartatua, bobble, bosse de nage, building jumper, buriedinspace, Christmas Ape, clouds, conductor, crash.landing, crooked, CudNylon, DasLofGang, deadbass, dogfight giggle, Double McDouble, Dr. Norm Thagard, draw, emotional fascism, esmuydavista, evan, Feech La Manna, Ferrous Bueller, freezinseason, Fullscreen, fury, glock mane, Google Adsense [Bot], grammatron, Grendel, guaranteed mediocrity, guidance, hatertime, Hideaway Lights, hideout, i am rich, iambic, inmate, inspectorhound, internethandle, jack, jett, joe, Kevin McCallister, Kiki, Kodiak, Lake, lame sayings, light rail coyote, loaf angel, Lonesome Hobo, Lowercase Tea, Magazine, marble, Mechanical bird, mich, Montague Terrace, Mr Spaceship, mworth, neely o'hara, nocents, Paul, plashy, Poptone, positive shit, Quigley, rap and country, resum, rex, shirts optional, Shotfrog, silverapples, Sissy Spacerock, Sobieski, spix et chicho, texas, tgk, thanks, That Demon Life, thrasher, twoboys, vivian darko, warmhouse, WeirdJungle, winjer, worrywort