Results 1 to 10 of 10

Thread: Relationship between tables doesn't seem to work for some values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Relationship between tables doesn't seem to work for some values

    Hi, I have two tables, one data table and one lookup table.

    However, the lookup table doesn't seem to work for all values of the lookup table below:

    Code:
    0
    1-5
    6-15
    16-19
    20-29
    30-39
    40-49
    50-64
    65-74
    75-79
    80-89
    90-w
    For lookup table value of 1-5 and 6-15 the relationship doesn't seem to work and the values gives me a total instead of a part of a sum. I am thinking that this could be because of sorting or something similar to that.

    Is there any workaround for this? In the image below, the 6-15 should give me 5235 and it gives me 44955 and I don't know why??


    https://ibb.co/r6QQG1y

    Last edited by Waimea; Jan 8th, 2019 at 12:40 AM.

  2. #2
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    Any suggestions on how to fix this problem? It really bothers me that I can't figure out why the lookup doesn't work!

  3. #3
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values


  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    Would you post the sample using one of the methods mentioned in:

    https://www.mrexcel.com/forum/about-...tachments.html
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    A B C
    1 City Age 2017
    14 City (Suburb 1) 0 1135
    15 City (Suburb 1) 1-5 4395
    16 City (Suburb 1) 6-15 5455
    17 City (Suburb 1) 16-19 1835
    18 City (Suburb 1) 20-29 14265
    19 City (Suburb 1) 30-39 15155
    20 City (Suburb 1) 40-49 8720
    21 City (Suburb 1) 50-64 10515
    22 City (Suburb 1) 65-74 5105
    23 City (Suburb 1) 75-79 1475
    24 City (Suburb 1) 80-89 1645
    25 City (Suburb 1) 90-w 410

    Column A is city name, column B is the same as my lookup table, column C is the population in that age group.

    The lookup table works for age 0, 16-19, 20-29, 30-39, 40-49, 50-64, 65-74, 75-79, 80-80 and 90-w.

    It doesn't work for age 1-5 and 6-15.
    Last edited by Waimea; Jan 8th, 2019 at 11:45 AM.

  6. #6
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    Could age 1-5 and 6-15 be interpreted as dates?

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    85,091
    Post Thanks / Like
    Mentioned
    44 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    Quote Originally Posted by Waimea View Post
    Could age 1-5 and 6-15 be interpreted as dates?
    Change the set up like below:


    ABCD
    1CityAge2017
    14City (Suburb 1)01135
    15City (Suburb 1)154395
    16City (Suburb 1)6155455
    17City (Suburb 1)16191835
    18City (Suburb 1)202914265
    19City (Suburb 1)303915155
    20City (Suburb 1)40498720
    21City (Suburb 1)506410515
    22City (Suburb 1)65745105
    23City (Suburb 1)75791475
    24City (Suburb 1)80891645
    25City (Suburb 1)90w410

    Sheet1

    Last edited by Aladin Akyurek; Jan 8th, 2019 at 12:44 PM.
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    Hi Aladin,

    I have changed my setup as per your very clever suggestion!

    I am using PowerPivot but I am unsure how to set up my lookup table?

    Code:
    2017 1-5:=CALCULATE([Population 2017] ;'Age'[Age] >="1" && 'Age'[Age] <="5")

  9. #9
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    I got it to work. Thank you very much Aladin!

    Your suggestion is really good and clever!

  10. #10
    Board Regular
    Join Date
    Jun 2018
    Posts
    273
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Relationship between tables doesn't seem to work for some values

    If someone has the time, please show me how to set up my lookup tablesfor this set up??

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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