#41

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

2 Likes
#42

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.

#43

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 :+1:

1 Like
#44

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.

1 Like
#45

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.

6 Likes
#46

:exploding_head::exploding_head::exploding_head:

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

Thanks everyone!

#47

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.

3 Likes
#48

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

1 Like
#49

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.

1 Like
#50

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.

1 Like
#51

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

2 Likes
#52

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

3 Likes
#53

I will always be addicted to the way a massive vlookup slows a spreadsheet to a crawl.

I also love the fact that after literally decades of using it I still always type ā€œFASLEā€ in the last argument first time round.

1 Like
#54

I bloody love an index/match. Big fan of doing a big index/match array across a bunch of cells and watching my laptop struggle to cope with the might of my formulas.

Cool and normal.

2 Likes
#55

So much for that.

#56

I tend to use AND/OR statements within IF to avoid lots of complex nesting.

#57

Always wrap your index/match with an iferror too, if you want to be a proper wanker like me

1 Like
#58

You want to know who deserves a special place in hell?

The person that built the table in one of my databases where one of the fields contains a mixture of valid values, NULLs and, magnificently, cells containing the text string ā€œNULLā€.

:angry:

2 Likes
#59

Iā€™ve been on quite a few IT courses and Iā€™m not sure any of them are particularly better than learning for free on YouTube, and a lot of them are much worse. It still amazes me how much you can learn for free in the 21st century and how many great people are giving up their time to record videos. I know they make money from YouTube either directly (advertising) or indirectly (getting people to sign up for paid courses etc), but a lot of the motivation is just that human desire to teach.

For Excel/Power Query/Power BI, here are a couple of YouTubers I really like:

1 Like
#60

The woman in that second video - work have paid for me to do one of the courses on her site. Itā€™s excellent.

The lead analyst/insight person at my work did one of her courses and found it useful, and heā€™s worked in analytics for decades.

He was interviewed on a podcast about his career recently - donā€™t want to state where I work anywhere public so wonā€™t post here, but if anyone is interested in it drop me a PM