excel - Excel Formula substitute similar values into a single formula
问题描述
I have set up this formula, For each cell it should subtract the contents from what i have stored in data!$B$1, in this case this is 300, so if the contents of "Time 1" is 100, this will display 200, this will then add up all of these values from Time 1 up to Time 15, this will also ignore anything that is not a number. Currently I have:
=SUM(IF(ISNUMBER([@[Time 1]]),data!$B$1-[@[Time 1]], 0),IF(ISNUMBER([@[Time 2]]),data!$B$1-[@[Time 2]], 0),IF(ISNUMBER([@[Time 3]]),data!$B$1-[@[Time 3]], 0),IF(ISNUMBER([@[Time 4]]),data!$B$1-[@[Time 4]], 0),IF(ISNUMBER([@[Time 5]]),data!$B$1-[@[Time 5]], 0),IF(ISNUMBER([@[Time 6]]),data!$B$1-[@[Time 6]], 0),IF(ISNUMBER([@[Time 7]]),data!$B$1-[@[Time 7]], 0),IF(ISNUMBER([@[Time 8]]),data!$B$1-[@[Time 8]], 0),IF(ISNUMBER([@[Time 9]]),data!$B$1-[@[Time 9]], 0),IF(ISNUMBER([@[Time 10]]),data!$B$1-[@[Time 10]], 0),IF(ISNUMBER([@[Time 11]]),data!$B$1-[@[Time 11]], 0), IF(ISNUMBER([@[Time 12]]),data!$B$1-[@[Time 12]], 0),IF(ISNUMBER([@[Time 13]]),data!$B$1-[@[Time 13]], 0), IF(ISNUMBER([@[Time 14]]),data!$B$1-[@[Time 14]], 0), IF(ISNUMBER([@[Time 15]]),data!$B$1-[@[Time 15]], 0))
This is essentially just
=SUM(IF(ISNUMBER([@[Time 1]]),data!$B$1-[@[Time 1]], 0))
but repeated for each Time. It seems extremely verbose for something that is practically using the same formula, could the same result be achieved through substituting "Time 1" with a list?
Here is a sample of the calculation currently:
Thanks in advance.
解决方案
推荐阅读
- javascript - 在这种情况下,为什么自定义插入符号不能始终正确定位?
- 3d - X3d 中的转换顺序
- python - 热图图例中的重叠标签-如何在 R 中的 ggplot 中移动图例标签?
- php - WordPress 类别页面上的 CSS 网格布局
- swift - 【核心数据】:数据:
- nginx - 带有 socks5 代理的 nginx proxy_pass?
- php - 如何在 PHP 中使用循环动态创建 html 表?
- python - 用另一个类方法装饰类方法
- swift - 如何在核心数据一对多关系的上下文中从表视图中删除行?(迅速)
- python - 为什么 os.path.join 返回带有 2 个斜杠的结果?