Results 1 to 7 of 7

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    country_name year computer_percapita
    United Kingdom 2002 0,400827706
    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:
    country_name year computer_percapita
    United States 1996 0,364269227
    United States 1997 0,406704277
    United States 1998 0,458306998
    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. workfile.xlsx

    Code:
    country_name year computer_percapita
    Argentina 1988 0,004400249
    Code:
    country_name year computer_percapita
    United States 1980 . (no data available)
    United States 1981 0,009198712
    United States .... ....
    United States 2002 0,670255482
    United States 2003 . (no data available)
    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,

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    901
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by th_kvt View Post
    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
    Attached Files Attached Files

  3. #3
    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!
    Attached Files Attached Files

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    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.

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by th_kvt View Post
    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
    Attached Files Attached Files

  6. #6
    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.

  7. #7
    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!
    Attached Files Attached Files
    Last edited by th_kvt; 2015-07-20 at 03:50 PM. Reason: forgot to explain that only the values of the US and UK are left in the excel file, all other values are removed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •