The Excel Thread

Fed up talking videogames? Why?
User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Thu Jan 04, 2018 9:47 am

I'm struggling with index match simply because it's easy to minus say row 49 from 49 across different sheets but when country takes over another in one sheet, i haven't yet figured how to have it fill down but automatically recognise that the value in row 49 in sheet two has moved to row 48. Shall perservere.

Really should do my actual work too :shifty:

Image
User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Thu Jan 04, 2018 10:45 am

strawberry float it, the vlookup function's good enough.

Anyone completed a good Excel course before? So many online ones that it's difficult to choose. Plus I'm not sure what level to sign up for, I'm not great but concerned that beginner ones would be like, 'here's how you save a file and type in data'.

Image
User avatar
1cmanny1
Member ♥
Joined in 2008
Location: New Zealand

PostRe: The Excel Thread
by 1cmanny1 » Fri Jan 05, 2018 3:18 am

Don't bother too much with an Excel course, just learn as you go. You can find anything by Googling, Excel is very popular.
Maybe just watch a few videos on how to work Lookups etc, Pivot Tables. The basics.

The best place where people love to help is here: https://www.reddit.com/r/excel/
Usually super helpful.

Qikzen wrote:
Death's Head wrote:
Qikzen wrote:
Death's Head wrote:
Qikzen wrote:YES YOUR SPREADSHEET YOU HAVE 20 WORKSHEETS ON WITH THOUSANDS OF CELLS ON ALL LINKED TO DIFFERENT SPREADSHEETS IS SLOW. strawberry float YOU STOP TELLING ME TO FIX IT I CANT.

One of the monthly reports I used to receive took 1 minute 40 seconds to open on my laptop. Once the laptop gets over the shock of what it is opening, it normally runs fines.


The difference is, you don't tell support to fix that right? You know that it's an issue you can't fix.
I rarely tell support anything because they are a bunch of clowns. Most of the time I will fix issues myself.


I don't know who your support are, but it's USUALLY the users who are the clowns. Just saiyan.


Nope 100% support people are the clowns. They don't know anything.
For example if they did, why would my Excel crash so much? I just want it to automatically send emails full of pictures, how am I asking too much?

Harry Ellis wrote:+1 for Index and Match.

My job basically requires me to live in Excel.

Excel. :wub:


I find this hard to believe. I have a hate/love relationship with Excel.
Although the hate is mainly due to the crashes/slow downs, which is due to support IT incompetence.

Image
User avatar
BTB
Member
Joined in 2008
Location: Ashtead

PostRe: The Excel Thread
by BTB » Fri Jan 05, 2018 9:37 am

I've started using google sheets a lot more compare to excel, basically the same but the query tool in sheets is very useful, a bit like SQL but I find it a lot easier for trial and error as well as there being a lot more help online with videos/forums etc.

But I do wish they actually taught you all the key excel functions (pivots, lookups etc) at school.

User avatar
KingK
Member
Joined in 2008

PostRe: The Excel Thread
by KingK » Fri Jan 05, 2018 9:47 am

I love Excel. It’s a very powerful piece of software and there’s always more to learn.

I’ve used MrExcel.com quite a lot in the past and can recommend it.

Re courses, skip any basic course as that will be new/open/save/close type stuff. Intermediate is where you should look if you really want to go on one but I find self teaching through trial and error and the occasional YT video or MrExcel thread helps me through.

I’m on iPad so can’t view your spreadsheet but the Index & Match, VLookup tips above are likely to be a good bet.

VB is awesome (I did a course 12 months ago) but never get to use it so have forgotten quite a lot. VB will sort it out for you I’m sure but someone else will need to help you I’m afraid. Sorry.

Good luck. I’m getting back to my spreadsheet now

Image
User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 12:37 pm

Rocksleddy wrote:[img]pasteboard.co/H1e5J5e.png[/img]

Image

Image

https://ibb.co/dBqKuw

Can’t even get the embedded img link to work :fp:

So anyway, how many excel whizzes in the house without a spouse. Sexual favours shall be provided for a solution.

I’m trying to write a macro that will automatically update my spreadsheet for week on week growth. To give an example, for the UK the last week's figures were 745, hence why there’s a 6 as the last number - it grew by 6 from last week.

To calculate the growth between sheets I’ve done a simple SUM calculation. I wrote a macro to do that too but realistically just doing one cell and filling down is quicker just now - what I really want is a macro that would take into account changing positions in the league table from country to country. Is that even possible?

Again to give an example, if Poland gained another user they would take over the Netherlands but currently my macro doesn’t realise this so would give incorrect growth figures for these two countries.

I’m hoping it’s not too difficult, just I’d forgotten that macros even existed in Excel until this morning. Any help would be mad appreciated.


Thought I'd give this a look and just trying to get my head around it.

First thing: I can't see a difference between the pictures, did you mean to only show it once but duplicated it by accident?

Secondly: Am I right in thinking this is one of many very similar sheets, one for each week, so each week the figure on the far right changes and basically column 5 is a sum of "previous week total (from previous sheet) + number in column 6"

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Sun Jan 07, 2018 12:56 pm

It's meant to be the same image I was just struggling to get it to show on the forum in any form.

Yeah there's a new sheet for every week - the client provides us with figures for column 5 so column 6 is the only one we need to deal with, which is the week on week growth. The simplest manual way to do it was to subtract this week's figures from last week's, then boom that's the growth. Was just trying to write a formula that would take into account when countries change rank so i could easily fill down the function and it would update automatically. Don't spend too much time thinking it through though, my Vlookup seems to do the job to an ok standard.

Image
User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 12:57 pm

Does it need to be a new sheet for each week?

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Sun Jan 07, 2018 12:58 pm

Yeah they like it screenshotted and sent over to them and want to be able to look back at previous weeks on the excel file.

Image
User avatar
Alvin Flummux
Member
Joined in 2008
Location: Wilmington, OH, USA
Contact:

PostRe: The Excel Thread
by Alvin Flummux » Sun Jan 07, 2018 1:37 pm

Is the MOS Excel certification worth getting?

Send me a postcard when you get to where you're going...
User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 2:19 pm

Rocksleddy wrote:Yeah they like it screenshotted and sent over to them and want to be able to look back at previous weeks on the excel file.


I think I've got something that might work well for you.

https://mega.nz/#!ZklTDLRC!6DlaTwm4tbLq ... -PL0XSwKsE

New data goes in the "RawData" sheet. Then on the "WeekView" sheet you choose the week you want and it put the country in order and tells you their total and how much was added that week.

Have a look and let me know.

User avatar
Rocsteady
Member
Joined in 2008

PostRe: The Excel Thread
by Rocsteady » Sun Jan 07, 2018 4:51 pm

That's not a growth wrote:
Rocksleddy wrote:Yeah they like it screenshotted and sent over to them and want to be able to look back at previous weeks on the excel file.


I think I've got something that might work well for you.

https://mega.nz/#!ZklTDLRC!6DlaTwm4tbLq ... -PL0XSwKsE

New data goes in the "RawData" sheet. Then on the "WeekView" sheet you choose the week you want and it put the country in order and tells you their total and how much was added that week.

Have a look and let me know.

Jesus man that looks like it took a while, i hope it didn't! That's awesome though gonna be studying it for a while to get it nailed down for the real stats tomorrow but even just looking at the functions used is interesting as.

Thanks mate, that's well class.

Image
User avatar
That's not a growth
Member
Joined in 2008

PostRe: The Excel Thread
by That's not a growth » Sun Jan 07, 2018 5:39 pm

No worries. If you have any issues figuring anything out give me a shout.


Return to “Stuff”

Who is online

Users browsing this forum: Bleachyleachy, Denster, Errkal, Godzilla, Hime, Qikz, Riksilver, Ste, The Watching Artist, Vermilion and 55 guests