Nearest value?

**Rman3349** · 07-15-2008, 12:20 PM

I don't know if a worksheet function exists for this, but here's what I want to do:

I have a list of numbers ranging from 6 million to about 300 million. I want to be able to find which of these values is closest to a value that I specify, say 95 million, and then be able to display which cell that lies in so i can use it in a formula. I have to do this hundreds of times so i would love to not have to do it manually. Does such a thing exist? Thanks for any help.

**NBVC** · 07-15-2008, 12:35 PM

Need more details...

Is your list in a single column are in a 3-d range? What exactly is the list range?

Is it closest, whether less than or greater than?

**olasa** · 07-15-2008, 01:11 PM

This might be in the right direction (see encl. file).

Hope it helped
Ola

**Rman3349** · 07-16-2008, 12:51 PM

Ok so here's some more detailed info. Essentially, I have data for most countries in the world specifying breakdowns of population and income of that sector of the population. An example of my raw data would be:

5 5 5
150 260 390 etc....

The 5s represent 5 percent of the population of these nations, and the numbers below them are this segment of the population's yearly income in local currency. After converting the currency, All i do is multiply each 5 percetile by the country's total population to get somthing like this:

6,123,456 12,123,456
some $ value some $ value etc....

So here's the issue. I know what percentage of the population live below certain $/day levels. A sample nation has say 58% of its population living below 1$/day. Multiplying this bu the nation's population gives my the number of people living below this threshold. This value will certainly not line up with the number of people at the boundry of a 5 percentile, and therefore, to find the exact value, I need to simply use a liner interpolation formula between the two 5 percentile population values which surround my number of people below the threshold.

I can't, however, simply specify a cell at which this occurs since for some countries it happens in the first 5 percentile while for some others it happens in the 15th. So what i want to do is tell excel this: "95,000,000 people in this country live below this threshold. From this list, find the value closest to 95,000,000 and tell me what cell that value is in. Then tell me the adjacent cell (so now I have the two cells it lies between) so I can run my formula"

Sorry for being so long-winded but that should be all the info any of you need. Thanks for tackling this problem for me. I promise your help will not unappreciated.