Page 15 of 29

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 Swelling Itching Brain
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.

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Fri Aug 03, 2018 11:49 am
by farmer
jewels, what are you trying to accomplish? or an example. your wording in that question is a bit vague.

Re: Ask Geoff About Excel

PostPosted: Sat Aug 04, 2018 10:29 am
by banquo
Geoff wrote:never mind I prepared myself

http://s000.tinyupload.com/?file_id=017 ... 7615720818

Just type into D4, it will insert the date and insert a row above, I think this is what you were after right?


just wanted to update that this has auto filled 800+ dates/times and also made that many new rows for me so far. thanks!

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sat Aug 04, 2018 5:36 pm
by Geoff
Woo, jewels would love to help

Re: Ask Geoff About Excel (or Google Sheets)

PostPosted: Sun Aug 05, 2018 10:46 pm
by jewels
farmer wrote:jewels, what are you trying to accomplish? or an example. your wording in that question is a bit vague.


I have text in 8 different text boxes, they are over a single jpg map. I want to shrink everything uniformly so the font size changes, the map size changes and everything remains proportionally the same, just 80% smaller.

When I try this, nothing goes right or how I expect it to.

I haven't used this dumb program in a decade and at my new job it seems like I'm going to be fixing up people's presentations and making them prettier.