Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Sorry if this posted twice - I received an error when trying to submit...
Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down...
=IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Hi Rick,
Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Sorry - "C399" represents the population, not the city name.
"Tammy" wrote: Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
The C3 was supposed to have been C2 (with no $ signs). Using your latest
post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Hi Rick,
Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Try the formula again, but this time change the 526 to the exact last row
that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Gosh, Rick, thanks so much for hanging in there with me.
The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Okay, here is what I did. I put your data in Rows 2 through 13 and used this
formula in F2 (notice the use of 13 as the maximum row number)... =IF(ABS(C2-LOOKUP(D$2,B$2:B$13,C$2:C$13))<500000,1,0) I copied that down to Row 13. The following cities were marked with a "1"... Houston city Philadelphia city San Diego city and the rest were marked with a "0", which is correct as Detroit city and Dallas city are not within 500,000 of Philadelphia city's population of 1,585,577). As far as I can see, the formula I posted is working correctly. If you cannot get it to work, send your worksheet to me so I can look at it (remove the NO.SPAM stuff from my address). -- Rick (MVP - Excel) "Tammy" wrote in message ... Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Hi Rick,
I copied and pasted the thirteen rows I posted to you from my original data. When I copied the formula, it did not find San Diego city. So, I went to the post, and copied and pasted the data directly from the post, then pasted the data into the proper columns and removed any trailing spaces. This time the formula worked! So, I went back and checked the original pasted thirteen rows, and did find trailing spaces after the city name - strange how it found Houston and Philadelphia even though they had trailing spaces, but not San Diego. As soon as I removed the trailing space for San Diego, the formula update to a 1 for that city. I went back to the original dataset and again tried the formula you sent that would account for any trailing spaces after the city names, but the results return a #VALUE error. I will send you the spreadsheet. If you think you can tell what the problem is by reading the above, just let me know. This has been so helpful, thank you. P.S. I'm going to indicate that your posts have answered my question, but I'm not sure if a check mark with appear. Every time I've posted and responded I get an error telling me that the post has not been sent. I haven't been notified that you've responded (even though I've checked the "notify me of replies" box), I just have to keep checking back and refreshing my browser. I don't know how MS keep tracks of helpful people like yourself, and I'd really like you to get credit for this. "Rick Rothstein" wrote: Okay, here is what I did. I put your data in Rows 2 through 13 and used this formula in F2 (notice the use of 13 as the maximum row number)... =IF(ABS(C2-LOOKUP(D$2,B$2:B$13,C$2:C$13))<500000,1,0) I copied that down to Row 13. The following cities were marked with a "1"... Houston city Philadelphia city San Diego city and the rest were marked with a "0", which is correct as Detroit city and Dallas city are not within 500,000 of Philadelphia city's population of 1,585,577). As far as I can see, the formula I posted is working correctly. If you cannot get it to work, send your worksheet to me so I can look at it (remove the NO.SPAM stuff from my address). -- Rick (MVP - Excel) "Tammy" wrote in message ... Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Please send the workbook to me.
As for your PS... you don't have to mark Yes or No for me... as an Microsoft MVP for Excel, Microsoft automatically tracks my participation in the newsgroups. However, you should feel free to continue marking the helpfulness of others (especially when a responder's signature line asks for it). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, I copied and pasted the thirteen rows I posted to you from my original data. When I copied the formula, it did not find San Diego city. So, I went to the post, and copied and pasted the data directly from the post, then pasted the data into the proper columns and removed any trailing spaces. This time the formula worked! So, I went back and checked the original pasted thirteen rows, and did find trailing spaces after the city name - strange how it found Houston and Philadelphia even though they had trailing spaces, but not San Diego. As soon as I removed the trailing space for San Diego, the formula update to a 1 for that city. I went back to the original dataset and again tried the formula you sent that would account for any trailing spaces after the city names, but the results return a #VALUE error. I will send you the spreadsheet. If you think you can tell what the problem is by reading the above, just let me know. This has been so helpful, thank you. P.S. I'm going to indicate that your posts have answered my question, but I'm not sure if a check mark with appear. Every time I've posted and responded I get an error telling me that the post has not been sent. I haven't been notified that you've responded (even though I've checked the "notify me of replies" box), I just have to keep checking back and refreshing my browser. I don't know how MS keep tracks of helpful people like yourself, and I'd really like you to get credit for this. "Rick Rothstein" wrote: Okay, here is what I did. I put your data in Rows 2 through 13 and used this formula in F2 (notice the use of 13 as the maximum row number)... =IF(ABS(C2-LOOKUP(D$2,B$2:B$13,C$2:C$13))<500000,1,0) I copied that down to Row 13. The following cities were marked with a "1"... Houston city Philadelphia city San Diego city and the rest were marked with a "0", which is correct as Detroit city and Dallas city are not within 500,000 of Philadelphia city's population of 1,585,577). As far as I can see, the formula I posted is working correctly. If you cannot get it to work, send your worksheet to me so I can look at it (remove the NO.SPAM stuff from my address). -- Rick (MVP - Excel) "Tammy" wrote in message ... Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Tammy
I believe that for Rick's LOOKUP formula to work your cities need to remain in alphabetic order. With the city of interest in D1, the list of cities in column A, the populations in column B, and the following formula in column C, I get results that should allow you to use an Autofilter to get a list of cities that meet your criteria. =IF(ABS(B2-VLOOKUP($D$1,$A$2:$B$13,2,FALSE))<500000,1,0) The FALSE argument will necessitate an exact match of your city with one in the list. If you use your city column as a validation list for the city in D1 and keep your cities in alphabetic order it is unnecessary. Good luck. Ken Norfolk, Va On Feb 12, 3:15*pm, Tammy wrote: Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI * * * * * * * * * * * *TOTPOP * * * * * *Search City 36 * * *New York city * *7,322,564 * * * * * *Philadelphia city 6 * * * Los Angeles city * * * * 3,485,398 * * * 17 * * *Chicago city * * 2,783,726 * * * 48 * * *Houston city * * 1,630,672 * * * 42 * * *Philadelphia city * * * *1,585,577 * * * 6 * * * San Diego city * 1,110,549 * * * 26 * * *Detroit city * * 1,027,974 * * * 48 * * *Dallas city * * * * * * * * * * *1,006,831 * * * 4 * * * Phoenix city * * 983,403 * * * * 48 * * *San Antonio city * * * * 935,927 * * * * 6 * * * San Jose city * *782,225 * * * * 24 * * *Baltimore city * 736,014 * * * * In the results, after filtering for "1" we should see only the following: 48 * * *Houston city * * 1,630,672 * * * 42 * * *Philadelphia city * * * *1,585,577 * * * 6 * * * San Diego city * 1,110,549 * * * 26 * * *Detroit city * * 1,027,974 * * * 48 * * *Dallas city * * * * * * * * * * *1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- *of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions!- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Ken is correct... the list would need to be sorted (I see that the list
isn't sorted from the workbook the OP sent to me at my request). Here is your formula modified to reference the OP's columns and row range and to allow for partial (case insensitive) matches... =IF(ABS(C2-VLOOKUP($D$2&"*",$B$2:$C$526,2,FALSE))<500000,1,0) -- Rick (MVP - Excel) wrote in message ... Tammy I believe that for Rick's LOOKUP formula to work your cities need to remain in alphabetic order. With the city of interest in D1, the list of cities in column A, the populations in column B, and the following formula in column C, I get results that should allow you to use an Autofilter to get a list of cities that meet your criteria. =IF(ABS(B2-VLOOKUP($D$1,$A$2:$B$13,2,FALSE))<500000,1,0) The FALSE argument will necessitate an exact match of your city with one in the list. If you use your city column as a validation list for the city in D1 and keep your cities in alphabetic order it is unnecessary. Good luck. Ken Norfolk, Va On Feb 12, 3:15 pm, Tammy wrote: Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions!- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
A HUGE thanks to you, Rick, for taking the time with me. The knowledge that
people like you share is invaluable. Thank you! "Rick Rothstein" wrote: Ken is correct... the list would need to be sorted (I see that the list isn't sorted from the workbook the OP sent to me at my request). Here is your formula modified to reference the OP's columns and row range and to allow for partial (case insensitive) matches... =IF(ABS(C2-VLOOKUP($D$2&"*",$B$2:$C$526,2,FALSE))<500000,1,0) -- Rick (MVP - Excel) wrote in message ... Tammy I believe that for Rick's LOOKUP formula to work your cities need to remain in alphabetic order. With the city of interest in D1, the list of cities in column A, the populations in column B, and the following formula in column C, I get results that should allow you to use an Autofilter to get a list of cities that meet your criteria. =IF(ABS(B2-VLOOKUP($D$1,$A$2:$B$13,2,FALSE))<500000,1,0) The FALSE argument will necessitate an exact match of your city with one in the list. If you use your city column as a validation list for the city in D1 and keep your cities in alphabetic order it is unnecessary. Good luck. Ken Norfolk, Va On Feb 12, 3:15 pm, Tammy wrote: Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions!- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a list based on a +/- of a cell value
Hi Ken,
Thank you for your input! " wrote: Tammy I believe that for Rick's LOOKUP formula to work your cities need to remain in alphabetic order. With the city of interest in D1, the list of cities in column A, the populations in column B, and the following formula in column C, I get results that should allow you to use an Autofilter to get a list of cities that meet your criteria. =IF(ABS(B2-VLOOKUP($D$1,$A$2:$B$13,2,FALSE))<500000,1,0) The FALSE argument will necessitate an exact match of your city with one in the list. If you use your city column as a validation list for the city in D1 and keep your cities in alphabetic order it is unnecessary. Good luck. Ken Norfolk, Va On Feb 12, 3:15 pm, Tammy wrote: Gosh, Rick, thanks so much for hanging in there with me. The last row where a city name is located *is* row 526 (populations end in the same row). I tried the formula listed below, but received the same results as before. Here is some new data to use as a reference (I sorted the data Z-A on the population column so that you could *see* the results we should get : STATEFP ANPSADPI TOTPOP Search City 36 New York city 7,322,564 Philadelphia city 6 Los Angeles city 3,485,398 17 Chicago city 2,783,726 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 4 Phoenix city 983,403 48 San Antonio city 935,927 6 San Jose city 782,225 24 Baltimore city 736,014 In the results, after filtering for "1" we should see only the following: 48 Houston city 1,630,672 42 Philadelphia city 1,585,577 6 San Diego city 1,110,549 26 Detroit city 1,027,974 48 Dallas city 1,006,831 Since we were searching for cities with populations +/- 500,000 of Philadelphia city population (sorry, I did change the city reference on you) - only those cities that had a population between 1,085,577 and 2,085,577 should display. Does this help? What more can I provide? thanks thanks thanks! "Rick Rothstein" wrote: Try the formula again, but this time change the 526 to the exact last row that data there is a city name in (I mistakenly told you that the last row in the range could be any number greater than the last row with data... LOOKUP appears to be more sensitive than that). IF this is what you already have done, then try this formula instead... =IF(ABS(C2-LOOKUP(D$2&"*",B$2:B$526,C$2:C$526))<=500000,1,0) It will ignore an trailing spaces that you may have attached to your city names. If that doesn't work, then post some *real* example data from your worksheet (I notice that your original sample population data seems to be in a different range than what you originally posted... possibly the problem might be we are working with different data). -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks, again, for your help! I pasted and copied your formula as is (no tweaking this time), however am still getting inaccurate results. (so sorry about this). The cities and populations in my results for "1" have populations between 12,818 - 516,259 and the results for the "0" populations are between 574,283 - 7,322,564 The results, after filtering for "1", should display only cities with a population greater than or equal to 1 million and less than or equal to 2 million. (between 1 and 2 million: 1,500,000 (Birmingham population) - 500,000 = 1,000,000 AND 1,500,000 (Birmingham population) + 500,000 = 2,000,000). Any other suggestions? Thanks so much! "Rick Rothstein" wrote: The C3 was supposed to have been C2 (with no $ signs). Using your latest post, put this formula on Row 2 somewhere and copy it down... =IF(ABS(C2-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) -- Rick (MVP - Excel) "Tammy" wrote in message ... Hi Rick, Thanks so much for your response! This will be great, if I can get it to work. I keep coming up with all zeros, so i'm doing something wrong. I did "tweak" the formula to fit my data. Here is what i have entered: =IF(ABS($C$399-LOOKUP(D$2,B$2:B$526,C$2:C$526))<500000,1,0) **I assumed your "C3" in the formula represented where "Birmingham city" was located in my example below. I changed C3 to the actual cell that contained that city name - I also changed it to an absolute value because the formula changed as it was copied down (c3, c4, c5, etc.) if I didn't use an absolute. If that was wrong, and C3 should be a relative reference, or actually represents something else, please let me know. **I changed the lookup range to the actual data range. **I changed the "less than" value to 500,000 (I used 100,000 yesterday to keep things related to the data I submitted.) Assuming Birmingham's population is 1,500,000, the lookup should return a true value of "1" if another city's population falls between 1,000,000 and 2,000,000. (+/- 500,000 of Birmingham's population) When I copy this down, I get all zeros - there are some cities that fall within the 1-2 million range. The formula looks like it looks up the value in D2 in the range B2:B526. The population in C2:C526 is used for the "less than" value(?). One thing I can't figure out from by looking at the formula, where does it take into account the value of Birmingham's population and search for the +/- 500,000? Should there be another nested IF function to represent the "greater than" side? This is such a great start! I appreciate you taking the time to answer. Can you help me a little further with this one? Thanks! "Rick Rothstein" wrote: Assuming you have header (so your data starts in Row 2) and assuming the search city's name is in D2, put this formula in a blank column starting on Row 2 and copy it down... =IF(ABS(C3-LOOKUP(D$2,B$2:B$1000,C$2:C$1000))<100000,1,0) Change the ranges row of 1000 to a number representing the maximum row number you could have data in. You can now Auto Filter on the 1. -- Rick (MVP - Excel) "Tammy" wrote in message ... Sorry if this posted twice - I received an error when trying to submit... Hi, Sorry about the weird subject, I wasn't sure how to word this one. Here is some data to use as a guide: STATEFP ANPSADPI TOTPOP 1 Anniston city 26623 1 Bessemer city 33497 1 Birmingham city 200000 1 Decatur city 48796 1 Dothan city 53583 1 Florence city 36521 1 Gadsden city 42523 1 Huntsville city 159866 1 Mobile city 196278 This is just a small selection from the data - STATEFP is column A / ANPSADPI is column B / TOTPOP is column C. My user would like to find a city - Birmingham City for example - and find the related population for that city. Then, return a list of cities and their populations that fall within 100,000 +/- of the this city (Birmingham). If Birmingham's population is 200,000, list any cities where their population falls between 100,000 and 300,000. The end result should display the list of cities and their populations. I feel like I can do this by using an advanced filter, but am not sure how to have excel look for the +/- data within the populations. Or is there a function that could work this out? Please help my brain with this one! It's starting to hurt! Thanks for any suggestions!- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a list based on +/- of a cell value | Excel Worksheet Functions | |||
Return value based on fraction of a list | Excel Worksheet Functions | |||
Return a list of cells based on data in a single cell. | Excel Worksheet Functions | |||
Return value from list based on criteria | Excel Worksheet Functions | |||
Return a price based on a customer from a drop down list | Excel Discussion (Misc queries) |