英文:
How to do Gap analysis in Excel
问题
I have below data format in excel:
我在Excel中有以下数据格式：
I would like to perform a gap analysis which must automatic show the 'gap' filled as below.
我想进行缺口分析，必须自动显示如下填充的“缺口”。
I tried this formula: =RIGHT(J35,1)-RIGHT(K35,1)
我尝试了这个公式：=RIGHT(J35,1)-RIGHT(K35,1)
But, I'm getting which says #VALUE!
但是，我得到的是#VALUE!的错误。
EDIT: I'm trying it in this way.
编辑：我正在以这种方式尝试。
=IF(OR(RIGHT((J35,1),"None","Done"),RIGHT(J35,1)-RIGHT(K35,1),"-1","Done"))
=IF(OR(RIGHT((J35,1),"None","Done"),RIGHT(J35,1)-RIGHT(K35,1),"-1","Done"))
Still problem.
仍然有问题。
Is this required a VBA code? It seems, keeping output of if statement as formula in excel is not possible now.
是否需要VBA代码？似乎现在无法在Excel中将if语句的输出保留为公式。
Any other ways? How to achieve this ( the required details as shown in gap column)? Thanks.
还有其他方法吗？如何实现这一点（如缺口列中所示的所需详细信息）？谢谢。
英文:
I have below data format in excel:
I would like to perform a gap analysis which must automatic show the 'gap' filled as below.
I tried this formula: =RIGHT(J35,1)-RIGHT(K35,1)
But, I'm getting which says #VALUE!
EDIT: I'm trying it in this way.
=IF(OR(RIGHT((J35,1),"None","Done"),RIGHT(J35,1)-RIGHT(K35,1),"-1","Done"))
Still problem
Is this required a VBA code? It seems, keeping output of if statement as formula in excel is not possible now.
Any other ways? How to achieve this ( the required details as shown in gap column)? Thanks.
答案1
得分: 1
以下是翻译好的内容：
似乎对IF和OR公式的使用不正确。我认为你想这样做：
如果J35的最右字符是数字，则减去J35-K35的最右字符
如果J35是“None”，则为-1
如果J35是“Done”，则为“Done”
这是一个嵌套的IF公式。第一个公式在所有版本的Excel中都可用，第二个公式在较新版本中可用。
=IF(ISNUMBER(VALUE(RIGHT(J35,1))),RIGHT(J35,1)-RIGHT(K35,1),IF(J35="None",-1,IF(J35="Done","Done")))
=IFS(ISNUMBER(VALUE(RIGHT(J35,1))),RIGHT(J35,1)-RIGHT(K35,1),J35="None",-1,J35="Done","Done")
英文:
It seems like an incorrect use of both the IF and OR formulas. I believe you're trying to do it like this:
If the right character of J35 is a number, subtract the right characters of J35-K35
If J35 is "None", -1
If J35 is "Done, "Done"
This is a nested IF formula. The first formula is available in all versions of Excel, the second formula is available in newer versions.
=IF(ISNUMBER(VALUE(RIGHT(J35,1))),RIGHT(J35,1)-RIGHT(K35,1),IF(J35="None",-1,IF(J35="Done","Done")))
=IFS(ISNUMBER(VALUE(RIGHT(J35,1))),RIGHT(J35,1)-RIGHT(K35,1),J35="None",-1,J35="Done","Done")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论