#38

@wileycat for this particular issue canā€™t you use Countifs?

#39

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.

#40

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

#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