Nov 11 2021 04:08 PM
Hello,
in the attached excel sheet, i am trying to improve it. In sheet "Calcs-P-Delta", Cells K25-K42, for each cell, it finds the in sheet J5-2 Column H, the closest value to the value in column E of "Calcs-P-Delta". For some reason in row 37 of "Calcs-P-Delta", it cant find a value that is equal or close to 29686. You see a value of 29.686 in row 37, but in my equation I multiply the value by 1000 because the values in J5-2 sheet has the 1000 built into the values. I am not sure how to fix the lookup or if there is a better way or function to do the . I appreciate any assistance!
Regards,
Sam
Nov 12 2021 02:36 AM
MATCH('Calcs-p1'!E37*1000,'J5-2'!H:H,1) will only return a meaningful result if the lookup range 'J5-2'!H:H is sorted in ascending order. It clearly isn't.
Try this in K24:
=INDEX('J5-2'!$H$2:$H$1728,MATCH(MIN(ABS('Calcs-p1'!E24*1000-'J5-2'!$H$2:$H$1728)),ABS('Calcs-p1'!E24*1000-'J5-2'!$H$2:$H$1728),0))/1000
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.
Nov 12 2021 07:07 AM - edited Nov 12 2021 07:08 AM
Thank you so much, Hans!
I made the change and it worked.
1. You used row H1728 as an upper limit, Is there a way to go up to the last value in column "I" that is a non zero. So in this case it will be row 1063. This value is a variable.
2. The other issue that I ran into is in column N in "Calcs-P-Delta" sheet( I renamed the sheet). In row 28, I don't see the value in cell K28=14.2314 or 14,231 present in sheet J5-2 column H. i was trying to investigate why "Actual delta" in column N of sheet "Calcs-P-Delta" is zero in row 28. My equation might need correction. Please see attached.
Thank you,
Sam
Nov 12 2021 07:47 AM
1) This is not really important - you could also use H10000 as upper limit.
2) 14231.4 is found in H1067 on the Calcs-P-Delta sheet. I1067 = 0, hence the result.
Nov 12 2021 08:19 AM
Nov 12 2021 11:02 AM
SolutionTry this:
=INDEX('J5-2'!$H$2:$H$1728,MATCH(MIN(IF('J5-2'!$I$2:$I$1728<>0,ABS(E24*1000-'J5-2'!$H$2:$H$1728),1E+300)),ABS(E24*1000-'J5-2'!$H$2:$H$1728),0))/1000
Nov 12 2021 11:43 AM - edited Nov 12 2021 11:43 AM
Hans,
I ran into this issue in row 29 of "Calcs-P-Delta", in column "N" it couldn't look up the corresponding actual deflection and it shows "N/A". Please see attached.
Nov 12 2021 12:04 PM
See the attached version.
Nov 12 2021 12:22 PM
Nov 12 2021 01:29 PM
ABS(E29*1000-'J5-2'!$H$2:$H$5000) is the absolute value of the difference between E29*1000 and the values in column H on the other sheet. (Absolute value changes negative differences to positive ones)
IF('J5-2'!$I$2:$I$5000<>0,ABS(E29*1000-'J5-2'!$H$2:$H$5000),1E+300) takes these differences but replaces 0 values with an extremely large number 1E+300 = 1 followed by 300 zeros.
MIN(IF('J5-2'!$I$2:$I$5000<>0,ABS(E29*1000-'J5-2'!$H$2:$H$5000),1E+300)) computes the minimum values of these numbers. Because 0s have been replaced with a large number, the 0s are skipped.
Nov 12 2021 11:02 AM
SolutionTry this:
=INDEX('J5-2'!$H$2:$H$1728,MATCH(MIN(IF('J5-2'!$I$2:$I$1728<>0,ABS(E24*1000-'J5-2'!$H$2:$H$1728),1E+300)),ABS(E24*1000-'J5-2'!$H$2:$H$1728),0))/1000