Ask Geoff About Excel (or Google Sheets)

Let's talk Aguachile Alley

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: 12594
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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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?
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 8959
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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 686
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: 3257
Joined: Mon Dec 14, 2009 4:09 pm

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: 12594
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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 686
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: 12004
Joined: Fri Oct 20, 2017 1:49 pm
Location: the world wide web

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

Ok
User avatar

Jsn
 
Posts: 686
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: 12594
Joined: Sat Dec 26, 2009 8:33 am

Postby Swelling Itching Brain » 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

Swelling Itching Brain
 
Posts: 33171
Joined: Tue Dec 15, 2009 12:53 am
Location: the wang bar

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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
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: 12594
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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
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?
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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

I mostly use KNIME to access mysql databases
User avatar

Geoff
i like jazz
 
Posts: 12594
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: 12594
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: 29159
Joined: Mon Dec 14, 2009 3:33 pm

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

jewels, what are you trying to accomplish? or an example. your wording in that question is a bit vague.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

Postby banquo » Sat Aug 04, 2018 10:29 am

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!
User avatar

banquo
 
Posts: 6549
Joined: Tue Dec 15, 2009 8:28 pm

Postby Geoff » Sat Aug 04, 2018 5:36 pm

Woo, jewels would love to help
User avatar

Geoff
i like jazz
 
Posts: 12594
Joined: Sat Dec 26, 2009 8:33 am

Postby jewels » Sun Aug 05, 2018 10:46 pm

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.
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: 29159
Joined: Mon Dec 14, 2009 3:33 pm

PreviousNext

Return to Aguachile Alley

Who is online

Users browsing this forum: A WOMAN IN TROUBLE, Albatross, Althea, Architecture, beardhurts, bluelips, brent, Brother Kenny, CelticFC#1, chewy, crams, creedence tapes, dadrocksampler, dan, Dirty Penny, Double McDouble, dr. badvibes, dragon jeans, endoskeleton, Executive Producer, Eyeball Kid, flimsy, Flossed Out, gobot, goldsoundz, Google [Bot], Grumby, hbb, i am rich, Jaboticaba, Kuma, launchpad, Littlelulusfanclub, Merciel, Milk, mynameisdan, No Good Advice, papi chulo, powderfinger, rankoutsider, rich uncle skeleton, speakers, transitive, warmjets and 307 guests