我需要在Excel中创建一个有条件的公式，包括相对和非相关条件在内

发布于 2025-02-10 18:51:27 字数 1483 浏览 1 评论 0原文

我在AI中具有AD列中的临界等级列 - 每个列具有1、2、3、4、5，N/A或空白。 AJ列只是一个占位符列，A列AK是我进行计算以提出重新平衡的临界等级，通过将每个评级从前六列乘以该评分区域的重量，在我的情况下，这些权重为六列中的30％，20％，20％，10％，15％，5％，这些权重在第729行中列出。我在处理727行数据时有两个目标：

如果有一个'n/a '或列中的空白，从这些列中获取百分比，然后将它们重新分配给其他列。因此，在图像中的第一行中，评分为5，空白，2、1、1、5，权重为30％，20％，20％，10％，15％，5％。如果没有重新分配，则公式为（5 * 30％） +（空白 * 20％） +（2 * 20％） +（1 * 10％） +（1 * 15％） +（5 * 5％）= 1.5 + 0 + .4 + .1 + .15 + .25 = 2.4，这是占位符AJ列。我的公式确定n/a和空白汇总到代表20％的列，其中有5列具有值，因此我为这5列中的每一个添加了4％，而我的新数学变为（5 * 34％）） +（空白 * 24％） +（2 * 24％） +（1 * 14％） +（1 * 19％） +（5 * 9％）= 1.7 + 0 + .48 + .48 + .14 + .19 + .19 + .45 = 2.96，这是重新平衡的AK列。一切都很好，我在Excel中使用的配方是：

= LET（总计，Sumif（AD2：AI2，“ N/A”，$ AD $ 729：$ AI $ 729）+SUMIF（AD2：AI2，“”，$ AD $ 729 ：$ ai $ 729），count，countifs（ad2：ai2，“＆gt; = 1”，ad2：ai2，'＆lt; = 5“），sum（ifError（（（ad $ 729：ai $ 729+$ 729+total/count/count）*ad2 ：ai2,0）））

我的问题是关于下一个目标：

如果列中有“ n/a”或空白，请从这些列中获取百分比，然后将其重新分配给它们在其原始权重上，其他列。因此，在图像中的第一行中，评分为5，空白，2、1、1、5，权重为30％，20％，20％，10％，15％，5％。如何编写公式，以便在这种情况下，根据AD和AI中AI中的列的百分比，通过AI添加了代表AF中的空白中空白中空白的20％？我仍然需要知道“总”，这是重新分配的百分比，在这种情况下为20％。但是我如何查看其余列，在这种情况下，这将是30％，20％，10％，15％，5％和重新分配20％相对于彼此相同吗？列广告以30％的速度开始，Ag列的开始为10％，A linter AH的启动为15％，因此AD需要重量三倍AG和AH的两倍，我需要考虑到任何数量的列（最多6个）都可以有空白或N/A的事实。在这种情况下，新的加权百分比将为37.5％，0％，25％，12.5％，18.75％，6.25％。

提前致谢！

原文

I have columns of criticality ratings in Columns AD through AI – each column has either a 1, 2, 3, 4, 5, N/A, or is blank. Column AJ is simply a placeholder column and Column AK is where I do my calculations to come up with a rebalanced criticality rating by multiplying each of the ratings from the first six columns by the weight of that rating area – in my instance, those weights are 30%, 20%, 20%, 10%, 15%, 5% across the six columns and these weights are listed in Row 729. I have two goals in processing the 727 rows of data:

If there is an ‘N/A’ or a blank in a column(s), take the percentages from those columns and reassign them equally to the other columns. So, in the first row in the image, the ratings are 5, BLANK, 2, 1, 1, 5 and the weights are 30%, 20%, 20%, 10%, 15%, 5%. If there was no reassignment, the formula would be (5 * 30%) + (BLANK * 20%) +(2 * 20%) + (1 * 10%) + (1 * 15%) + (5 * 5%) = 1.5 + 0 + .4 + .1 + .15 + .25 = 2.4, which is the placeholder AJ column. My formula determines that the N/A and blank add up to columns representing 20% and there are 5 columns that have values in them, so I add 4% to each of those 5 columns, and my new math becomes (5 * 34%) + (BLANK * 24%) +(2 * 24%) + (1 * 14%) + (1 * 19%) + (5 * 9%) = 1.7 + 0 + .48 + .14 + .19 + .45 = 2.96, which is the rebalanced AK column. Everything works great and the formula I use in Excel is:

=LET(total,SUMIF(AD2:AI2,"N/A",$AD$729:$AI$729)+SUMIF(AD2:AI2,"",$AD$729:$AI$729),count,COUNTIFS(AD2:AI2,">=1",AD2:AI2,"<=5"),SUM(IFERROR((AD$729:AI$729+total/count)*AD2:AI2,0)))

My question is regarding the next goal:

If there is an ‘N/A’ or a blank in a column(s), take the percentages from those columns and reassign them based on their original weights to the other columns. So, in the first row in the image, the ratings are 5, BLANK, 2, 1, 1, 5 and the weights are 30%, 20%, 20%, 10%, 15%, 5%. How do I write the formula so that in this instance the 20% representing the BLANK in Column AF is added to AD and AF through AI based on the percentages of the columns in AD and AF through AI? I would still need to know ‘total’, which is the percentage to reassign, and in this case is 20%. But how do I look at the remaining columns, which in this case would be 30%, 20%, 10%, 15%, 5%, and reassign the 20% such that the new percentages would be the same relative to each other? Column AD starts out at 30%, Column AG starts out at 10%, and Column AH starts out at 15%, so AD needs to wind up with a weight three times as much as AG and twice as much as AH, and I need to account for the fact that any number of columns (up to all 6) can have blanks or N/A’s. In this instance the new weighted percentages would be 37.5%, 0%, 25%, 12.5%, 18.75%, 6.25%.

Thanks in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三岁铭 2025-02-17 18:51:27

那以下疯狂的公式呢？

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

假定为第2行的重量调整为第2行。将其存储为百分比（因此，如果将单元格式设置为一般，则将30％显示为0,3）。

首先构建，

如果合适的话，它首先返回给定单元的基本重量：

^{重量 *和（ value ＆lt;＆gt; 0， value value < /strong>＆lt;＆gt;“ n/a”）}

AD$729*AND(AD2<>0,AD2<>"N/A")

，对行的每个单元重复：

^{cell 1 + 先前的cell公式的先前公式2 + 细胞3的先前公式 + 细胞4 + 先前的公式对于单元6}，

(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A"))

用于划分价值和重量的乘积：

^{value * stroge /先前的公式< /strong>}

AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A"))

实际上仅在必要时才会衡平：

^{if（or（ value =“ n/a”， value = 0），0），0 ，以前的公式）}，

IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

该行重复：

^{细胞1 + 先前的公式/strong> + 细胞3 + 的先前公式6}

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

编辑：注释的请求

我们需要动态更改对第729行的（适当单元格中的（适当单元格）。为了实现此目的，我们将使用此公式：

^{indirect（硬编码列＆amp;行（车辆列表的左上方单元格） - 1 +匹配（带有给定车辆的类型，车辆清单，0 ）}

INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))

我们将适应所需的每个单元格：

先前的参考	新公式
AD $ 729	间接（“ ad” ）
AE $ 729	间接（“ AE”＆amp; row（$ ad $ 729）-1+Match（B2，$ b $ 729：$ b $ 734,0））
AF $ 729	间接（“ AF”＆amp; row（$ $ aD $ 729） -1+Match（B2，$ B $ 729：$ B $ 734,0））
AG $ 729	间接（“ AG”＆amp; row（$ ad $ 729）-1+Match（B2，$ B $ 729：$ B $ 734,0））
ah $ 729	间接（“ ah”＆amp; row（$ ad $ 729）-1+match（b2，$ b $ 729：$ b $ 734,0））
ai $ 729	间接（“ ai”＆amp; row（$ $ $ $ 729） - 1+匹配（B2，$ b $ 729：$ b $ 734,0））

因此，我们将能够相应地替换它们并获得此信息：

=IF(OR(AD2="N/A",AD2=0),0,AD2*INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))

第二次编辑，

我将其重新编写为公式，将其调整为新请求。尝试一下：

=IF(OR(AL2="N/A",AL2=0),0,AL2*INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AM2="N/A",AM2=0),0,AM2*INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AN2="N/A",AN2=0),0,AN2*INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AO2="N/A",AO2=0),0,AO2*INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AP2="N/A",AP2=0),0,AP2*INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))

在这里一张表使范围更改的表更容易：

A	B	C	D	E	F	G
	重量单板	1
	重量排	729
	重量列	Al	=左（cell（单元格（“地址”，偏移），偏移（c3＆amp;“ 1”），0,1），2,99），find（“ $”，中间（“ address”，offset（c3＆amp;“ 1”），0,1），2,99）） - 1 ）	=左（中间（单元格（“地址”，偏移）（间接（D3＆amp;“ 1”），0,1）），2,99），find（“ $”，mid cell（cell（“地址”，offset））间接（d3＆amp;“ 1”），0,1），2,99）） - 1）	=左（中间（单元格（“地址”），偏移（间接（E3＆amp;“ 1”），0,1）））））））））），2,99），find（“ $”，中间（“地址”，偏移（e3＆amp;“ 1”），0,1），2,99）） - 1）） - 1）	=左（中（Mid）单元格（“地址”，偏移（间接（F3＆amp;“ 1”），0,1），2,99），查找（“ $”，mid（cell（“地址”，偏移），偏移（f3＆amp;“ 1） ”），0,1），2,99）） - 1）
	值表	1
	值行	2
	值列	Al	=左（中间（单元格）（“地址”，偏移（c6＆amp;“ 1”），0,1），2,99），find（“ $”，MID（MID）（单元格（“地址”，偏移（间接（C6＆amp;“ 1”），0,1），2,99）） - 1）	=左（cell（cell（“地址”），偏移（间接偏移（D6＆amp;“ 1）） “），0,1），2,99），find（“ $”，cell（“地址”，偏移（D6＆amp;“ 1”），0,1），2,99）），2,99）））））））））） -1）	=左（中间（单元格（“地址”，偏移），偏移（e6＆amp;“ 1”），0,1），2,99），find（“ $”，mid（cell（“地址”，“地址”，”）偏移（间接（E6＆amp;“ 1”），0,1），2,99））-1）	=左（中间（单元格（“地址”，偏移），偏移（f6＆amp;“ 1”），0,1 ），2,99），find（“ $”，中间（“地址”，偏移（间接（F6＆amp;“ 1”），0,1），2,99）） - 1））-1））-1）
	案例表	案例
	行	2
	列列	B
	重量指数地址	！


	1	表1 （“＆amp; $ c $ 1＆amp;“！”！＆amp; $ c $ 3＆amp;“ $” C $ 9＆amp; $ C $	8 ＆amp; row（“＆amp; $ c $ 1＆amp;“！”！＆amp; $ c $ 3＆amp;“ $”＆amp; $ c $ 2＆amp;“） - 1+match（“＆amp; $ c $ 7＆amp;“！”！＆amp; $ c $	9 “”“”＆amp; row（“＆amp; $ c $ 1＆amp;“！”＆amp; $ c $ 3＆amp; amp;“ $”＆amp; $ c $ 2＆amp;“） - 1+match（“＆amp; $ c $ 7＆amp; amp; “！	” f3＆amp;“”＆amp; row（“＆amp; $ c $ 1＆amp;“！”！！	$ 7 “＆amp; g3＆amp;“”＆amp; row（“＆amp; $ c $ 1＆amp;“！”＆amp; $ c $ 3＆amp; “ $”＆amp; $ c $ 2＆amp;“） - 1+匹配（“＆amp; $ c $ 7＆amp;“！”＆amp; $ c $ 9＆amp; $ c $ 8＆c $ 8＆amp;“，”，“”＆amp; $ c $ 10＆amp; amp;“，0）） “
	值	= $ c $ 4＆amp;“！” ＆amp; c6＆amp; $ c $ 5	= $ c $ 4＆amp;“！” ＆amp; d6＆c $ 5	= $ c $ 4＆amp;“！” ＆amp; e6＆amp; $ c $ 5	= $ c $ 4＆amp;“！” ＆amp; f6＆amp; $ c $ 5	= $ c $ 4＆amp;“！” ＆amp; g6＆c $ 5

重量 *和（value＆lt;＆gt; 0，value＆lt;＆lt;＆	lt	;＆gt;＆gt;＆lt; 0，“＆amp; c14＆amp;”＆lt;＆gt;“” n/a“”）“	= d13＆amp;” ＆lt;＆gt;“” n/a“”）“	= e13＆amp;” a“”）“	= f13＆amp;“*”＆amp;“ and（”＆amp; f14＆amp;＆lt;＆gt;＆gt; 0，“”＆amp; f14＆amp;＆amp;＆lt	; “*”＆amp;“ and（”＆amp; g14＆amp;“＆lt;＆gt;＆gt; 0，”＆amp; g14＆amp;“＆lt
; +细胞3+单元4+先前的公式的先前公式，用于细胞5	公式2	= c16＆amp; amp; amp; d16＆amp; amp;“+”+”＆amp; e16＆amp; amp;“+”+“ f16＆amp; f16＆amp; amp;
价值*重量/先前公式	3	= C14＆amp;“*”＆amp; c13＆amp;“/”/“＆amp;”（“＆amp; $ c17＆c17＆amp;“）”	= d14＆amp;“”“*”＆amp; d13＆amp; d13＆amp; amp; amp; （“＆amp; $ c17＆amp;“）”	= e14＆amp;“*”＆amp; e13＆amp;“/”/“＆amp;”（“＆amp; amp; $ c17＆amp;“）”	= f14＆amp;“*” ;“（”（“＆amp; $ c17＆amp;“）”	= g14＆amp;“*”＆amp; g13＆amp;“/”/“＆amp;”（“＆amp; $ c17＆amp; amp;”）'if
（or（value =“ n/a”，“”，“”，“”，“”，“”，值= 0），0，以前的公式）	公式4	=“ if（或（”＆amp; c14＆amp;“ =”“ n/a”，“，”＆amp; c14＆amp;“ = 0），0），0，＆amp; amp; c18＆amp; “）”	=“ if（或（”＆amp; d14＆amp;“ =”“ n/a”，“”，“”＆amp; d14＆amp;“ = 0），0，”，“”＆amp; d18＆amp;“）”	= ”＆amp; e14＆amp;“ =”“ n/a”，“”，“”＆amp; e14＆amp;“ = 0），0，“”＆amp; e18＆amp;“）”	=“ if（或（或（或（或”＆（“”＆（＆amp; f14 n/a“”，“”，＆amp; f14＆amp;“ = 0），0，”，“”＆amp; f18＆amp;“）”	=“ if（or（或（”＆（“＆amp; g14＆amp;” =“ n/a”“ n/a”，“”，“＆amp; g14＆amp;“ = 0），0，“”，＆amp; g18＆amp;“）”
单元1 +单元格2 +先前公式的先前公式2 + cell 3 + cell 4 +先前的公式4 +先前的公式5个单元	格5	= c19＆amp ;“+”＆amp; d19＆amp;“+”＆amp; e19＆amp;“+”＆amp; f19＆amp;“+”+“ g19

Crazy Formulas都需要疯狂的编辑方式。

What about the following crazy formula?

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

This one is tuned for the row 2. Weight are assumed to be stored as percentage (therefore if the cell format is set as general, 30% will be shown as 0,3).

The building up

First it return the base weight for the given cell if it's appropriate:

^{weight * AND(value<>0, value<>"N/A")}

AD$729*AND(AD2<>0,AD2<>"N/A")

which is repeated for each cell of the row:

^{previous formula for cell 1 + previous formula for cell 2 + previous formula for cell 3 + previous formula for cell 4 + previous formula for cell 5 + previous formula for cell 6}

(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A"))

which is used to divide the product of value and weight:

^{value * weight / previous formula}

AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A"))

which is actually calulated only if necessary:

^{IF(OR(value = "N/A", value = 0), 0, previous formula)}

IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

which is repeated for each cell of the row:

^{previous formula for cell 1 + previous formula for cell 2 + previous formula for cell 3 + previous formula for cell 4 + previous formula for cell 5 + previous formula for cell 6}

=IF(OR(AD2="N/A",AD2=0),0,AD2*AD$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*AE$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*AF$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*AG$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*AH$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*AI$729/(AD$729*AND(AD2<>0,AD2<>"N/A")+AE$729*AND(AE2<>0,AE2<>"N/A")+AF$729*AND(AF2<>0,AF2<>"N/A")+AG$729*AND(AG2<>0,AG2<>"N/A")+AH$729*AND(AH2<>0,AH2<>"N/A")+AI$729*AND(AI2<>0,AI2<>"N/A")))

EDIT: comment's request

We need to dynamically change the reference to the (appropriate cell in the) row 729. To achieve this, we will use this formula:

^{INDIRECT( hardcoded column & ROW( top-left cell of the vehicle list ) - 1 + MATCH( cell with the kind of the given vehicle , vehicle list , 0 ))}

INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))

which we will adapt for each cell needed:

Previous reference	New formula
AD$729	INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AE$729	INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AF$729	INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AG$729	INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AH$729	INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))
AI$729	INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))

so we will be able to substitute them accordingly and obtain this:

=IF(OR(AD2="N/A",AD2=0),0,AD2*INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AE2="N/A",AE2=0),0,AE2*INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AF2="N/A",AF2=0),0,AF2*INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AG2="N/A",AG2=0),0,AG2*INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AH2="N/A",AH2=0),0,AH2*INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))+IF(OR(AI2="N/A",AI2=0),0,AI2*INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))/(INDIRECT("AD"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AD2<>0,AD2<>"N/A")+INDIRECT("AE"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AE2<>0,AE2<>"N/A")+INDIRECT("AF"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AF2<>0,AF2<>"N/A")+INDIRECT("AG"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AG2<>0,AG2<>"N/A")+INDIRECT("AH"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AH2<>0,AH2<>"N/A")+INDIRECT("AI"&ROW($AD$729)-1+MATCH(B2,$B$729:$B$734,0))*AND(AI2<>0,AI2<>"N/A")))

Second edit

I've re-written the formula tuning it for the new request. Try it:

=IF(OR(AL2="N/A",AL2=0),0,AL2*INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AM2="N/A",AM2=0),0,AM2*INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AN2="N/A",AN2=0),0,AN2*INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AO2="N/A",AO2=0),0,AO2*INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))+IF(OR(AP2="N/A",AP2=0),0,AP2*INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))/(INDIRECT("AL"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AL2<>0,AL2<>"N/A")+INDIRECT("AM"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AM2<>0,AM2<>"N/A")+INDIRECT("AN"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AN2<>0,AN2<>"N/A")+INDIRECT("AO"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AO2<>0,AO2<>"N/A")+INDIRECT("AP"&ROW(AL$729)-1+MATCH(B2,$AK$729:$AK$734,0))*AND(AP2<>0,AP2<>"N/A")))

Here a table to make the range change easier:

A	B	C	D	E	F	G
	weight sheet	Sheet1
	weight row	729
	weight column	AL	=LEFT(MID(CELL("address",OFFSET(INDIRECT(C3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(C3&"1"),0,1)),2,99))-1)	=LEFT(MID(CELL("address",OFFSET(INDIRECT(D3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(D3&"1"),0,1)),2,99))-1)	=LEFT(MID(CELL("address",OFFSET(INDIRECT(E3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(E3&"1"),0,1)),2,99))-1)	=LEFT(MID(CELL("address",OFFSET(INDIRECT(F3&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(F3&"1"),0,1)),2,99))-1)
	value sheet	Sheet1
	value row	2
	value column	AL	=LEFT(MID(CELL("address",OFFSET(INDIRECT(C6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(C6&"1"),0,1)),2,99))-1)	=LEFT(MID(CELL("address",OFFSET(INDIRECT(D6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(D6&"1"),0,1)),2,99))-1)	=LEFT(MID(CELL("address",OFFSET(INDIRECT(E6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(E6&"1"),0,1)),2,99))-1)	=LEFT(MID(CELL("address",OFFSET(INDIRECT(F6&"1"),0,1)),2,99),FIND("$",MID(CELL("address",OFFSET(INDIRECT(F6&"1"),0,1)),2,99))-1)
	case sheet	Sheet1
	case row	2
	case column	B
	weight index address	Sheet1!$AK$729:$AK$734


	weight	="INDIRECT(""" & $C$1 &"!" & C3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))"	="INDIRECT(""" & $C$1 &"!" & D3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))"	="INDIRECT(""" & $C$1 &"!" & E3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))"	="INDIRECT(""" & $C$1 &"!" & F3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))"	="INDIRECT(""" & $C$1 &"!" & G3 & """&ROW(" & $C$1 &"!" & $C$3 & "$" & $C$2 &")-1+MATCH(" & $C$7 &"!" & $C$9 & $C$8 &"," & $C$10 &",0))"
	value	=$C$4&"!" &C6&$C$5	=$C$4&"!" &D6&$C$5	=$C$4&"!" &E6&$C$5	=$C$4&"!" &F6&$C$5	=$C$4&"!" &G6&$C$5

weight * AND(value<>0, value<>"N/A")	formula 1	=C13&"*"&"AND("&C14&"<>0,"&C14&"<>""N/A"")"	=D13&"*"&"AND("&D14&"<>0,"&D14&"<>""N/A"")"	=E13&"*"&"AND("&E14&"<>0,"&E14&"<>""N/A"")"	=F13&"*"&"AND("&F14&"<>0,"&F14&"<>""N/A"")"	=G13&"*"&"AND("&G14&"<>0,"&G14&"<>""N/A"")"
previous formula for cell 1 + previous formula for cell 2 + previous formula for cell 3 + previous formula for cell 4 + previous formula for cell 5	formula 2	=C16&"+"&D16&"+"&E16&"+"&F16&"+"&G16
value * weight / previous formula	formula 3	=C14&"*"&C13&"/"&"("&$C17&")"	=D14&"*"&D13&"/"&"("&$C17&")"	=E14&"*"&E13&"/"&"("&$C17&")"	=F14&"*"&F13&"/"&"("&$C17&")"	=G14&"*"&G13&"/"&"("&$C17&")"
IF(OR(value = "N/A", value = 0), 0, previous formula)	formula 4	="IF(OR("&C14&"=""N/A"","&C14&"=0),0,"&C18&")"	="IF(OR("&D14&"=""N/A"","&D14&"=0),0,"&D18&")"	="IF(OR("&E14&"=""N/A"","&E14&"=0),0,"&E18&")"	="IF(OR("&F14&"=""N/A"","&F14&"=0),0,"&F18&")"	="IF(OR("&G14&"=""N/A"","&G14&"=0),0,"&G18&")"
previous formula for cell 1 + previous formula for cell 2 + previous formula for cell 3 + previous formula for cell 4 + previous formula for cell 5	formula 5	=C19&"+"&D19&"+"&E19&"+"&F19&"+"&G19