Data Analysis / Project Management 😴

[zanimos]

Depending on what you are doing Pivot Tables might be a more useful way of summarizing/analyzing your data? It’s a good tool that tends to get a bit overlooked I think.

Apols if that’s too obvious or already been mentioned.

[colossalhorse]

I found a couple of courses on Udemy good for expanding my Excel knowledge - they’re usually pretty cheap and you can find courses as broad or specific as you like. You don’t get a certificate to add to your CV but if you just want the knowledge I’ve found them useful.

[carnivalboy]

Microsoft has some good free online courses, including Power BI. Head over to learn.microsoft.com and sign up for a course. I’m doing the Power BI one and it’s pretty good.

Both Power BI and Excel use Power Query, so learning that will help you with both. I’m pretty new to all this, but my initial understanding is that Excel is better if you want to view numbers, and Power BI is better if you want to view pretty pictures, but it’s not like you need to study one or the other.

If you’re a motivated self-starter you get Prince2 Foundation certificate just by buying the course manual and teaching yourself. I tried that but I am the opposite of a motivated self-starter, especially as Prince2 is just about the most boring thing I have ever tried to study.

[still_here]

In all seriousness I’ve got to basically an advanced level in Excel purely by googling stuff I want to do. I reckon that’s probably just as good a way to learn as a training course, if not better.

For HR purposes for a small or medium sized company you definitely won’t need more than excel and Power BI. Eventually you might want to build up your confidence to start using macros, but by that point you’ll basically be doing magic as far as your colleagues are concerned

[HobbesWhenHeWasNew]

@wileycat

(I have tried several times over the last few weeks to get a count of cells in a certain column in three worksheets that have a date of more than 90 days ago - still failing every time I try and online suggestions aren’t working

For something like this, if I was struggling to get it to work in one formula across 3 sheets, I’d do the count separately in each sheet, then just add the 3 totals together in the dashboard, as a work around. Sorry if you’ve already tried this.

[still_here]

@wileycat for this particular issue can’t you use Countifs?

[wileycat]

That’s what I’ve been trying, but the formulas to get the right dates using TODAY haven’t been working (they either seem to count all of them or none of them). There’s obviously something I’m missing, but as I don’t quite understand how the bits of a formula fit together it’s difficult to tell where I’m going wrong.

I think you’re probably right - I just need to get my head around the structure of formulas and how multiple conditions fit together to be able to apply things I’ve searched for to my spreadsheet. I’ll have a look for a guide on those fundamentals.

[harru]

Can you make a cell with today’s date, say cell z1, then formula is something like countif range(date < z1 -90 ) ?

Don’t know how to format it properly with “” and the like, or the proper syntax. I hate excel tbh

I learnt some sql though and that’s quite fun

[still_here]

I would have the date you want in a separate cell, where the formula you use is:
="<"&TODAY()-90

Then in the cell you want to count, use this formula (column B is the first column you want to count, cell c1 is the cell with the date of today minus 90 days:
=COUNTIFS(B:B,$C$1,

Then repeat for the second and third columns

[wileycat]

I’ll try this - I don’t think it’s the different sheets where it’s going wrong though as I’ve tried a similar formula for conditional formatting relative to time elapsed on one sheet and it hasn’t worked either.

I think there’s an Excel help thread here isn’t there? I’ll erase the data and post what I’m trying to do there later.

[wileycat]

Ah, that’s interesting, I’ve been trying to do the minus 90 in the countif - didn’t think of doing it in separate cell. I’ll have a go later and report back

[HobbesWhenHeWasNew]

You can make it easier by doing another column which copies the dates, but shows them in their number format (today is 44280.00) then doing the same with the Today command. Then a cell with today in number form - 90, then a countif of less than that number.

[colinfilth]

fwiw, and without wanting to turn this into another Excel thread, this kind of touches on one of my pet peeves about the way some people use Excel. There’s a certain type of person that wants to put everything in their spreadsheet in one cell containing a massive complicated formula (not talking about still_here’s formula here by the way), whereas the bonus of spreadsheets is you have masses and masses of cells available to you to do your calculations in small steps. And that often makes solving a problem easier. You can always hide the working cells for presentation purposes.

[wileycat]

Definitely going to make use of the Excel thread going forwards as I do often need things breaking down / explaining a bit.

Thanks everyone!

[colossalhorse]

It does feel pretty good to get a monster formula working though doesn’t it? I agree it’s usually sub-optimal but sometime I like to do it just to feel like a wizard. I am a cool and normal person.

[colinfilth]

Yes, but when we do this we are evil wizards and we know it

[HobbesWhenHeWasNew]

The thing with excel is that it’s actually a really good tool, but you need to know what it can actually do for it to be useful.

I. E. Vlookup is really good for cutting out nesting ifs, but its useless unless someone’s told you about it.

For the above thing with dates, if you know that the date in a computer is just a number counting up from a certain point in history, and you can get excel to show it as that number, it makes all sorts of date manipulation much more straightforward.

[jont2001]

once you’re comfortable about using intermediate cells that get you to the answer you need, then actually joining them together into one formula isn’t usually too hard. Your ‘longhand’ method will give you the answer to check against as well, as a little validation check. I still tend to do this before making one large formula as they can be a bit of a pain to debug.

[colinfilth]

Vlookups and pivot tables, the trusty sword and shield of the Excel data analyst warrior!

[still_here]

This is such a wanky thing to say but imo index/match is better than vlookup