IF function with nested INDEX's & COUNT's

[Jmorrison67]

Good afternoon MrExcel community,

Subject: IF function with nested INDEX's & COUNT's

I have tried to search the forum's but can only find IF + COUNT's or IF + INDEX or IF + INDEX MATCH, so hopefully there is no duplication in questions.

I have a fairly complex formula which i am 1. trying to understand and 2. Looking to see if this is the best approach. the formula was set up a few years ago by a colleague who is no longer in the department, so trying to unpick it, is proving a bit of a challenge. I have tried as per below but just not following what all the nested INDEX and COUNT's are actually doing & would quite like to see if there is a simpler way of doing it.

The scenario
There are two results we need to see:

1. 1st year - this is the 1st year but only going back 10 years > so for example Site A started trading in 2009, but we only want data for 10 years, so from 2012 onwards. Then consider Site B - started trading in 2014, so want the formula to pick up the 1st year as 2014. Then consider Site C - started trading in 2016, so want the formula to pick up the 1st year as 2016.

This is the dataset:

I tried to break out each of the INDEX's to work out what they are doing:

The formula feels overly complex - does anyone know if there is an easier solution for it? Hopefully I have added as much data as possible, if you need anything else, then please reach out to me.



2. Last year - this should just be the latest year with values
I have slightly changed the data set just to show this scenario:

Suppose Site C closed at end of 2020 and there is no results in 2021, the formula should then return the 2020 results, but per above it returns zero, which isn't the result we need. Notice the 1st year is also incorrect and returns the 2nd year f trading value.

This is the formula for 'Last Year':

Note: This file will be rolled forward annually and will get new data in it every year.

Any help would be greatly appreciated

Kind regards
Jmorrison67

 

[jasonb75]

Looking at the formula, I would say that it was written by someone that thinks they know a bit about excel but in reality...
It's better if you post your formulas as text in the body of your post so that we don't have to try and read them from screen captures. It also means that we can copy and paste it to the reply box for reference instead of having to constantly scroll up and down while we retype it.
First

=INDEX(42:42,AGGREGATE(15,6,COLUMN($C$3:$O$3)/($C$3:$O$3>=$V$1)/($C42:$O42<>""),1))

Last

=LOOKUP(1e+100,$C42:$O42)

edit:- note that the first formula here is not the same as the one that you may have seen in your email notification, that one had an error in it.

 

[Jmorrison67]

Looking at the formula, I would say that it was written by someone that thinks they know a bit about excel but in reality...
It's better if you post your formulas as text in the body of your post so that we don't have to try and read them from screen captures. It also means that we can copy and paste it to the reply box for reference instead of having to constantly scroll up and down while we retype it.
First

=INDEX(42:42,AGGREGATE(15,6,COLUMN($C$3:$O$3)/($C$3:$O$3>=$V$1)/($C42:$O42<>""),1))

Last

=LOOKUP(1e+100,$C42:$O42)

edit:- note that the first formula here is not the same as the one that you may have seen in your email notification, that one had an error in it.

Good evening Jasonb75,

Firstly....Thanks - this is definitely better than the existing solution to the scenario! (i'll mark the above as the solution)
Secondly...Apologies on just dumping in screenshots, i did try to download the add in, but think only my company administrator can install the add in's and so tried to just grab as much data in the screenshot as possible. but 100% noted for future just to put the formula in the main body

It would be good to understand the AGGREGATE function a bit better having never used it before - would you be able to put a couple notes on what the formula is doing? I used the 'evaluate' function in excel to follow it through and got as far as - Likely completely wrong given i've never used it before, so go easy on me

First

=INDEX(42:42,AGGREGATE(15,6,COLUMN($C$3:$O$3)/($C$3:$O$3>=$V$1)/($C42:$O42<>""),1))

INDEX row 42 - row which has the values in it
AGGREGATE(15 - looking for smallest value ?
option 6 - is ignoring error values
COLUMN - assuming it is setting the formula to look from C3 through O3
then the next bit - this is where i lose how it works from here > is it then dividing each of the year values that are greater than value in cell V1 to find the starting point?
Then row 42 - probably not following this either, but 'less than' or 'greater than' blank, return TRUE

Last

=LOOKUP(1e+100,$C42:$O42)

I have also never used the '1e+100' before - what is that doing?

I only recently discovered MrExcel when I needed some help with Macro's & VBA on some other projects I was working on and it is absolutely amazing the help that we can get on issues! I speak highly of all the community that offer help on here - you are all so knowledgeable and every day is a school day for me, even in my mid 30's!!

Really appreciate your help on this

Kind Regards
Jmorrison67

 

[jasonb75]

then the next bit - this is where i lose how it works from here > is it then dividing each of the year values that are greater than value in cell V1 to find the starting point?

You're quite close with your evaluation, although some parts can be tricky to follow if you're not familiar with the methods.
($C$3:$O$3>=$V$1) is actually returning TRUE for each year that is >=V1 or FALSE for anything that is not.
Similarly, ($C42:$O42<>"") returns TRUE for cells that are not blank or FALSE for those that are blank. Although your way of describing it is not wrong, <> means not equal to.

The division part actually starts with the column numbers, divides those by the results of the first and second logical tests (the 2 parts described above), dividing by TRUE keeps the original column number, FALSE returns a #DIV/0! error for that column. Because aggregate has been told to ignore errors is will look at what remains and return the smallest column number that meets both criteria. Subsequently this is passed back to index to return the correct value from row 42.

I have also never used the '1e+100' before - what is that doing?

That is scientific notation for 1 followed by 100 zeros, a simple example would be 1e+6 is the same as 1 million.

Lookup works by finding the last match in the specified row / column that is less than or equal to the criteria specified. By using a ridiculously large number you will always get the last cell in the row / column that contains a numeric value.

Home that makes a bit of sense.

 

[Jmorrison67]

You're quite close with your evaluation, although some parts can be tricky to follow if you're not familiar with the methods.
($C$3:$O$3>=$V$1) is actually returning TRUE for each year that is >=V1 or FALSE for anything that is not.
Similarly, ($C42:$O42<>"") returns TRUE for cells that are not blank or FALSE for those that are blank. Although your way of describing it is not wrong, <> means not equal to.

The division part actually starts with the column numbers, divides those by the results of the first and second logical tests (the 2 parts described above), dividing by TRUE keeps the original column number, FALSE returns a #DIV/0! error for that column. Because aggregate has been told to ignore errors is will look at what remains and return the smallest column number that meets both criteria. Subsequently this is passed back to index to return the correct value from row 42.


That is scientific notation for 1 followed by 100 zeros, a simple example would be 1e+6 is the same as 1 million.

Lookup works by finding the last match in the specified row / column that is less than or equal to the criteria specified. By using a ridiculously large number you will always get the last cell in the row / column that contains a numeric value.

Home that makes a bit of sense.

Jason - this is a perfect explanation of both!
Really helpful!