I am trying to set up a sheet that I can use as a template to sort data, find perticular values and create a chart. Everything is working fine for the maximum magnitude and average magnitude values I am looking for. I am running into an issue in the formula in the images attatched. Below is the full code.
我正在尝试建立一个表，我可以用它作为一个模板来排序数据、查找特定值并创建一个图表。对于我正在寻找的最大震级和平均震级，一切正常。我遇到一个问题，在公式中的图像调谐。下面是完整的代码。
=VLOOKUP(MAX(ABS(INDIRECT("J"&P3&":J"&Q3))),INDIRECT("J"&P3&":M"&Q3),4,FALSE)
With this code I am fining the maximum value in the vertical direction, then returning the corresponding magnitude value.
使用此代码，我将在垂直方向上确定最大值，然后返回相应的大小值。
When I was just inputting the cell values it was working fine (taking a long time but it was working) however to make it quick and easy to apply to the other data files (they are in the same layout with the same number of values but have different values) I have adjusted it to the above formula.
当我输入单元格值是正常(在很长一段时间但工作)然而,让它快速和容易适用于其他数据文件(它们在同一个布局相同数量的值,但有不同的值)我已经调整到上面的公式。
In the P and Q columns I have found the row of the top and bottom of the ranges I am interested in and then have substituted these values into my working formula.
在P和Q列中，我找到了我感兴趣的范围的顶部和底部的行，然后将这些值代入我的工作公式中。
This method works great for the average magnitude and maximum magnitude so I know it should work, but when used the formula above it finds the incorrect value. For some reason it evaluates an array to a single value that is the second in the list.
这种方法对于平均大小和最大大小都很有效，所以我知道它应该有效，但是当使用上面的公式时，它会发现错误的值。由于某些原因，它将数组计算为一个值，而这个值是列表中的第二个值。
1: evaluated formula before the miss-step (the array that is the range I am looking for)
1:在miss-step前计算公式(该数组是我要查找的范围)
2: evaluated formula after the miss-step (3.5 which is not close to the maximum absolute value)
2:漏步后的计算公式(3.5，不接近最大值绝对值)
Any help would be great.
任何帮助都是伟大的。
1 个解决方案
#1
1
You cannot convert the max(abs(...)) and use that for the lookup; that only works for positive numbers. You need to pass processing to a second VLOOKUP if the first fails with IFERROR.
不能将max(abs(…))转换为查找;这只适用于正数。如果第一个用IFERROR失败，则需要将处理传递给第二个VLOOKUP。
You would be better off with a slightly more advanced sub-formula that can retrieve the absolute maximum than using an array formula to achieve the same.
您最好使用稍微高级一点的子公式，它可以检索绝对最大值，而不是使用数组公式来实现相同的结果。
Additionally, the non-volatile INDEX can replace the volatile INDIRECT.
此外，非挥发性指数可以替代挥发性间接指数。
As a non-array formula,
作为一个非数组公式,
=iferror(vlookup(max(max(index(j:j, p3):index(j:j, q3)), abs(min(index(j:j, p3):index(j:j, q3)))), index(j:j, p3):index(m:m, q3), 4, false),
vlookup(-max(max(index(j:j, p3):index(j:j, q3)), abs(min(index(j:j, p3):index(j:j, q3)))), index(j:j, p3):index(m:m, q3), 4, false))
In other words, if you cannot find the max(abs(...)) then look for the -max(abs(...)).
换句话说，如果你找不到max(…)，那就找-max(…)。
#1
1
You cannot convert the max(abs(...)) and use that for the lookup; that only works for positive numbers. You need to pass processing to a second VLOOKUP if the first fails with IFERROR.
不能将max(abs(…))转换为查找;这只适用于正数。如果第一个用IFERROR失败，则需要将处理传递给第二个VLOOKUP。
You would be better off with a slightly more advanced sub-formula that can retrieve the absolute maximum than using an array formula to achieve the same.
您最好使用稍微高级一点的子公式，它可以检索绝对最大值，而不是使用数组公式来实现相同的结果。
Additionally, the non-volatile INDEX can replace the volatile INDIRECT.
此外，非挥发性指数可以替代挥发性间接指数。
As a non-array formula,
作为一个非数组公式,
=iferror(vlookup(max(max(index(j:j, p3):index(j:j, q3)), abs(min(index(j:j, p3):index(j:j, q3)))), index(j:j, p3):index(m:m, q3), 4, false),
vlookup(-max(max(index(j:j, p3):index(j:j, q3)), abs(min(index(j:j, p3):index(j:j, q3)))), index(j:j, p3):index(m:m, q3), 4, false))
In other words, if you cannot find the max(abs(...)) then look for the -max(abs(...)).
换句话说，如果你找不到max(…)，那就找-max(…)。