在电子表格中使用IF公式 [英] Using IF formula in spreadsheets

问题描述

我有一个表格，下面的示例中，用户从选项列表中选择优先级"和复杂性"，并且根据第1列和第2列中项目的组合在等级"列中分配了一个数字.我尝试进行重做公式

I have a table, example below, where users select the Priority and Complexity from a list of options and a number is assigned in the Rank column based on the combination of items in columns 1 and 2. I tried to re-work the formula here, but I'm clearly missing something.

| Priority | Complexity | Rank |
| Low      | Complex    | 3    |
| Medium   | Complex    | 2    |
| High     | Unknown    | 1    |

The formula below works for anything with a low or high priority, but shows N/A for medium and I can't figure out why...

=IF(G46="High",IF(H46="Simple",1,IF(H46="Complex",2,IF(H46="Unknown",1))),IF(AND(G46="Medium",IF(H46="Simple",1,IF(H46="Complex",2,IF(H46="Unknown",2)))),IF(AND(G46="Low",IF(H46="Simple",2,IF(H46="Complex",3,IF(H46="Unknown",3)))))

解决方案

This formula makes me shudder a bit. I've never been a fan of huge nested IF formulas because they are difficult to read, fragile, and easy to mess up.

I highly recommend just making a small reference table instead, then using an INDEX/MATCH approach to lookup the appropriate value. See example below.

Note you can put the table where ever you want, you can even hide it if necessary.

这篇关于在电子表格中使用IF公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！