Matching the values closest to the index and reporting the difference in years

th_kvt · Jun 24, 2015

Hi everyone,

I am currently dealing with the following excel formula problem. I have included the workfile for convenience.

To explain the workfile, it includes data on the number of computers used per capita. To illustrate, the United Kingdom had in the year 2002 0,40082… computers per capita.

Code:

[TABLE="width: 367"]
[TR]
[TD="width: 209"]country_name[/TD]
[TD="width: 35"]year[/TD]
[TD="width: 123"]computer_percapita[/TD]
[/TR]
[TR]
[TD="width: 209"]United Kingdom[/TD]
[TD="width: 35"]2002[/TD]
[TD="width: 123"]0,400827706[/TD]
[/TR]
[/TABLE]

My goal is to see how many years the United Kingdom (and all other countries in the excel file) lag behind (or are ahead) of the United States.

So, if this number is compared to the United States, one can see that the value 0,40082 of the United Kingdom in 2002 is closest to the value 0,4067 of the United States (year=1997). In other words, the United Kingdom lags 5 years (2002 - 1997) = 5.

Code:

[TABLE="width: 367"]
[TR]
[TD]country_name[/TD]
[TD="align: right"]year[/TD]
[TD="align: right"]computer_percapita[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD="align: right"]1996[/TD]
[TD="align: right"]0,364269227[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD="align: right"]1997[/TD]
[TD="align: right"]0,406704277[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD="align: right"]1998[/TD]
[TD="align: right"]0,458306998[/TD]
[/TR]
[/TABLE]

So what should the formula do? It should compare the computers used per capita of every country for every single year in the workfile (C-column) to the United States (C-column completely at the bottom of the workfile). Then, it should see which year this is for the UK and the US and report the difference. The lag is positive if the US uses more computers per capita and negative if the US uses less computers per capita.

A restricting remark is that the value of for example the UK (and all other countries in the workfile) should lie between the lowest and highest value of the US. To illustrate, Argentina in 1988 used 0,0044 computers per capita, this value is never between the lowest and the highest value of the US and so it wouldn’t make sense if the formula reported a number. There is just no data to compare it to the US as it doesn’t lie within the interval (lowest, highest value of the US). Basically, the data of the United States is the restricting part as every country is compared to the US. View attachment workfile.xlsx

Code:

[TABLE="width: 367"]
[TR]
[TD="width: 209"]country_name[/TD]
[TD="width: 35, align: right"]year[/TD]
[TD="width: 123, align: right"]computer_percapita[/TD]
[/TR]
[TR]
[TD="width: 209"]Argentina[/TD]
[TD="width: 35, align: right"]1988[/TD]
[TD="width: 123, align: right"]0,004400249[/TD]
[/TR]
[/TABLE]

Code:

[TABLE="width: 367"]
[TR]
[TD="width: 209"]country_name[/TD]
[TD="width: 35, align: right"]year[/TD]
[TD="width: 123, align: right"]computer_percapita[/TD]
[/TR]
[TR]
[TD="width: 209"]United  States[/TD]
[TD="width: 35, align: right"]1980[/TD]
[TD="width: 123, align: right"]. (no data available)[/TD]
[/TR]
[TR]
[TD="width: 209"]United  States[/TD]
[TD="width: 35, align: right"]1981[/TD]
[TD="width: 123, align: right"]0,009198712[/TD]
[/TR]
[TR]
[TD="width: 209"]United  States[/TD]
[TD="width: 35, align: right"]....[/TD]
[TD="width: 123, align: right"]....[/TD]
[/TR]
[TR]
[TD="width: 209"]United  States[/TD]
[TD="width: 35, align: right"]2002[/TD]
[TD="width: 123, align: right"]0,670255482[/TD]
[/TR]
[TR]
[TD="width: 209"]United  States[/TD]
[TD="width: 35, align: right"]2003[/TD]
[TD="width: 123, align: right"]. (no data available)[/TD]
[/TR]
[/TABLE]

I hope some of you can help me out, I tried to explain it as clear as possible. I’m just stuck. When I finally have a formula that works, I will also try to incorporate a tolerance level (+/-), but I first want to make sure the formula works.

Thanks in advance,

navic · Jul 1, 2015

th_kvt said:
I hope some of you can help me out,

If I understood you well. I tried to solve your problem, see if it suits you

In to cell D2 put array formula copied down

Code:

=IF(IF(C2="";"";INDEX($B$11045:$B$11113;MATCH(MIN(ABS(C2-$C$11045:$C$11113));ABS(C2-$C$11045:$C$11113);0)))=1940;"";IF(C2="";"";INDEX($B$11045:$B$11113;MATCH(MIN(ABS(C2-$C$11045:$C$11113));ABS(C2-$C$11045:$C$11113);0))))

In to cell D2 put formula copied down

Code:

=IF(AND(B2<>"";D2<>"");B2-D2;"")

pay attention to the separator, if these formulas do not work instead of a semicolon (

you should put a comma (,)
see attach

th_kvt · Jul 3, 2015

Thanks Navic for your help. Your formula indeed helped me out! I've tried the same as you did, however I got some errors (probably some typos). Now it is finally working.
I've also been trying to incorporate to only look at values in the index of the US (so between the smallest and largest value reported). I'm not sure whether this is possible.

Code:

Smallest intensity value US : 0.00919871218502522
Largest intensity value US : 0.670255482196807

For now, I've created a work around. But I was wondering whether you know an option to only look at values between the smallest and largest value of the US and incorporating this into the formula.

Attached is the new workfile I created based on the formulas. I only left two countries in the excel file (namely Switzerland and the Philippines) as they each represent a value below the index of the US and above the index of the US. And otherwise the excel file became to large to upload it.

Thanks in advance for any help!

Hercules1946 · Jul 4, 2015

Hello th_kvt
I also spent time looking at this, managing to calculate the correct difference compared with the US, but so far I can't work out a way to bring the US year with it, which would then enable me to get the difference in years. Using navic's formula, I could only get #VALUE! whenever the was an entry in col C. I think this might have something to do with the default value(s) used in the empty cells in that column. If you are still haven't found a solution, I'm happy to play with it a bit more.

Hercules1946 · Jul 5, 2015

th_kvt said:
Thanks Navic for your help. Your formula indeed helped me out! I've tried the same as you did, however I got some errors (probably some typos). Now it is finally working.
I've also been trying to incorporate to only look at values in the index of the US (so between the smallest and largest value reported). I'm not sure whether this is possible.

Code:

Smallest intensity value US : 0.00919871218502522 Largest intensity value US : 0.670255482196807

For now, I've created a work around. But I was wondering whether you know an option to only look at values between the smallest and largest value of the US and incorporating this into the formula.

Attached is the new workfile I created based on the formulas. I only left two countries in the excel file (namely Switzerland and the Philippines) as they each represent a value below the index of the US and above the index of the US. And otherwise the excel file became to large to upload it.

Thanks in advance for any help!

Hello th_kvt
The attachment appears to do what you want. Like you, I'm posting a reduced file, and I think the changes I've made to Navics' original have made the formula shorter while adding the bit to exclude any data that is outside the US range.

I made the following amendments to your data:
1. Col C I cleared contents on all the empty cells, as with yours Cnnnnn = "" returns an error
2. In F2 and G2 I added the US per capita lower and upper limits

Hope that helps

th_kvt · Jul 20, 2015

Thank you Hercules1946 for your extra help. I've checked it for some other excel files as well and it appears to be working good!
Currently I'm testing and applying the formula to several excel files. If I notice an error or something else, I will post it. Thanks again.

th_kvt · Jul 20, 2015

Hi there again,

I do find another issue, an issue which I should have expected beforehand...

The issue is the following. As we compare each country for each year against the US index, it is necessary that the US index is always moving upward and not moving up - up - down - up - down - down for example. However, this is the case in some instances. I've included an excel file where this is the case. Given the years and lags reported, you can clearly see that this was not the value we wanted excel to report us, obviously. The only two countries included are the United States (the index) and the United Kingdom. All other values are removed to reduce the filesize of excel.

The reason of course is the it just looks up a value in the index of the US and reports the value closest to it, not thinking of anything else. However, I'm not sure how to solve it. I think it would be good to make the formula first look at the first value and the second value (is it between this value?), then second and third value (is it between this value?), then third and fourth value (is it between this value?), etc. etc. till it is between a certain value en then report the year and calculate the lag. After the value is found, it should not luck further down the index.

But I must say, I'm absolutely not sure how to solve this in excel

Hope anybody got an idea!

Matching the values closest to the index and reporting the difference in years

th_kvt

New member

navic

New member

Attachments

th_kvt

New member

Attachments

Hercules1946

New member

Hercules1946

New member

Attachments

th_kvt

New member

th_kvt

New member

Attachments