Page 15 of 19

Re: Ask Geoff About Excel (or Google Sheets)

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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 6:20 am
by Geoff
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!

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 6:22 am
by Geoff
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)

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 6:27 am
by identikit
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 7:33 am
by Geoff
glad to be of service, excel is really super easy.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 5:03 pm
by farmer
Woah just found this thread I'm gonna post some questions later

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 5:13 pm
by farmer
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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 5:33 pm
by farmer
Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 5:37 pm
by Robert
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 6:17 pm
by farmer
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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 11:36 pm
by Jsn
Geoff. Any tips for backtesting algorithms? I always find it to be a drag.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Thu Jul 12, 2018 11:45 pm
by lights
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Jul 13, 2018 4:47 am
by Geoff
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?

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Jul 13, 2018 10:28 am
by farmer
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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Jul 13, 2018 10:52 am
by farmer
Jsn wrote:Geoff. Any tips for backtesting algorithms? I always find it to be a drag.


What are you trying to do examplewise

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sat Jul 14, 2018 7:39 pm
by Jsn
Testing forecasting accuracy- by going back in time and running the algorithm with old data

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sat Jul 14, 2018 11:00 pm
by draw
Just learn some python my dude

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Jul 15, 2018 3:41 am
by Jsn
Ok

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Jul 15, 2018 4:24 am
by Geoff
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Jul 15, 2018 5:09 am
by Grey Poupon
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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Jul 15, 2018 12:51 pm
by farmer
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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Jul 15, 2018 6:54 pm
by Geoff
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

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Jul 15, 2018 6:56 pm
by Geoff
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?

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 10:21 am
by farmer
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 10:23 am
by farmer
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 10:52 am
by Geoff
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 11:13 am
by farmer
lol yeah i can see how that would work. thanks!

and what db software do you use instead of access?

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 11:38 am
by Geoff
I mostly use KNIME to access mysql databases

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 11:41 am
by Geoff
But I work for a tiny startup so not practical on scale

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 11:47 am
by jewels
Is it possible to group text and scale uniformly? The option to group is grayed out.