Closest Match Formula
Let me start with an example of data first...
12 25 2 9 ? I have 4 columns above. The first three columns are numbers and one of those needs go into a new column, which would be column 5. In column 5, I need to know which of the first three columns is the closest match to column 4 without going over. Can a formula be set up like this? This would be in Excel 2003. Thanks! Trisha |
Closest Match Formula
assuming values are in a1,b1,c1 and d1:
=MAX(IF(A1D1,0,A1),IF(B1D1,0,B1),IF(C1D1,0,C1)) "TJAC" wrote: Let me start with an example of data first... 12 25 2 9 ? I have 4 columns above. The first three columns are numbers and one of those needs go into a new column, which would be column 5. In column 5, I need to know which of the first three columns is the closest match to column 4 without going over. Can a formula be set up like this? This would be in Excel 2003. Thanks! Trisha |
Closest Match Formula
what if the data looks like...
12 6 2 9 ? do you want it to be 12 or 6? because both have difference of 3 from 9? -kc "TJAC" wrote: Let me start with an example of data first... 12 25 2 9 ? I have 4 columns above. The first three columns are numbers and one of those needs go into a new column, which would be column 5. In column 5, I need to know which of the first three columns is the closest match to column 4 without going over. Can a formula be set up like this? This would be in Excel 2003. Thanks! Trisha |
Closest Match Formula
the below formula gets you the first closest to D1
=IF(ABS(D1-A1)=MIN(ABS(D1-A1),ABS(D1-B1),ABS(D1-C1)),A1,IF(ABS(D1-B1)=MIN(ABS(D1-A1),ABS(D1-B1),ABS(D1-C1)),B1,C1)) -kc *Click Yes if this helps "KC" wrote: what if the data looks like... 12 6 2 9 ? do you want it to be 12 or 6? because both have difference of 3 from 9? -kc "TJAC" wrote: Let me start with an example of data first... 12 25 2 9 ? I have 4 columns above. The first three columns are numbers and one of those needs go into a new column, which would be column 5. In column 5, I need to know which of the first three columns is the closest match to column 4 without going over. Can a formula be set up like this? This would be in Excel 2003. Thanks! Trisha |
All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com