Logic Formula to calculate distance

**xury2000** · 07-07-2009, 12:56 PM

Hey all, I am a bit stuck on a project I am doing. I have a excel sheet with city names in Column A, Distances in Column B, and Distances in Column C.

I wanted to write a formula so excel checks the distance between column C and column B and if there is less than a 10% difference between the two then it would copy the name of that city into another column D.

Any help would be greatly appreciated, thanks!

**NBVC** · 07-07-2009, 01:00 PM

You mean something like:

=IF((C2-B2)/80<0.1,A2,"")

**xury2000** · 07-07-2009, 01:09 PM

Originally Posted by NBVC

You mean something like:

=IF((C2-B2)/80<0.1,A2,"")

I tried that formula but the problem is that instead of just checking 1 cell minus the other cell, it needs to check the entire Column C minus entire Column C and if it fits then 10% criteria only then does it Copy the city name.

I tried to modify your formula into:

=IF((C20:C4896-B13)/B13<0.1,A20:A4896,"")

B13 is a static value that I have

However excel gave me an value error.

Also on a related value, is there anyway for excel to check for the second least value instead of the MIN value in a list of values?

**ChemistB** · 07-07-2009, 01:18 PM

The formula that NBVC gave you needs to be copied down Column D. In each row, it will copy over the name of the city if it meets your criteria.
Also, I'm not sure what you have in B13, but if it's relevent to the formula, you need to make it absolute ($B$13) before dragging the formula down so it doesnt change.

In reference to your second smallest number, if your list is in B2:B500,

Please Login or Register  to view this content.

where the 2 indicates second smallest number.
Any of that help?

**NBVC** · 07-07-2009, 01:21 PM

Originally Posted by ChemistB

Also, I'm not sure what you have in B13, but if it's relevent to the formula, you need to make it absolute ($B$13) before dragging the formula down so it doesnt change.

That was my mistake... I was using the hard entered values...should be

=IF((C2-B2)/C2<0.1,A2,"")

copied down...