请在excel 2016 mac中更正我的IF功能 [英] Please correct my IF function in excel 2016 mac

查看:147
本文介绍了请在excel 2016 mac中更正我的IF功能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我已经制作了IF函数的公式,看看,如果该值等于我想要的值,那么从单元格A中取一个值并乘以单元格B.如果我想要做的是下面的一个简单示例。以下是嵌套if函数的示例。



= IF(J15 =3mm,B16 * K15,IF(J15 =4mm,B17 * K15,IF(J15 =5mm,B18 * K15,IF(J15 =10mm,B19 * K15))))

J15是下拉菜单,其中包含所有值

B16:B19是我想要收到的价值,如果J15是真的

我想多个B16:B19和K15



问题在于下面更大的公式有大约16个嵌套ifs。如果我写代码它工作正常但是我关闭excel并打开它的那一刻,这写成= #VALUE!而不是我的代码。以下是有问题的公式



 = IF(J28 =  < span class =code-string> 1 / 2_Inch / 1.5mm,$ D $ 58 * K28,IF(J28 =   3/4_Inch / 1.2mm,$ D $ 59 * K28,IF(J28 =   3/4_Inch / 1.5mm,$ D $ 60 * K28,IF(J28 =   1_Inch / 1.2mm,$ D $ 61 * K28,IF(J28 =   1_Inch / 1.5mm ,$ D $ 62 * K28,IF(J28 =   1x2_Inch / 1.2mm, $ D $ 63 * K28,IF(J28 =   1x2英寸/1.5毫米,$ D $ 64 * K28,IF(J28 =   1.5x1.5_Inch / 1.5mm,$ D $ 65 * K28, IF(J28 =   1.5x1.5_Inch / 2mm,$ D $ 66 * K28,IF(J28) =   2x2_Inch / 1.5mm,$ D $ 67 * K28,IF(J28 =   2x2_Inch / 2mm,$ D $ 68 * K28,IF(J28 =   3x3_Inch / 3mm,$ D $ 69 * K28,IF(J28 =   3x3_Inch / 4mm,$ D $ 70 * K28,IF(J28 =   4x4_Inch / 4mm,$ D $ 71 * K28,IF(J28 =   4x4_Inch / 5mm,$ D $ 72 * K28,IF(J28 =   4x4_Inch / 6mm,$ D $ 73 * K28))) ))))))))))))







其他细节



1)1 / 2_Inch / 1.5mm& 3 / 4_寸/ 1.2mm都在一个下拉菜单中

J28是下拉值



2)细胞D58到D73具有我想要的值的单元格J28值是真的

3)我将D:58中的单元格乘以K28与D73相乘。



同样奇怪的是,如果我把这个公式放在windows excel中它根本不起作用,但它适用于Mac,如果我把它全部重复或者我只是删除一些引号并把它放回去



我使用的是办公室2016 mac,我也有windows 2016 excel



我尝试了什么:



我试图从引号中删除所有空格但仍然不起作用

解决方案

< blockquote> D


58 * K28,IF(J28 = 3/4_Inch /1.2mm

d

I have made a formula of IF function to see that, If the value is equal to what i want then, then take a value from cell A and multiply by cell B. A simple example if what i want to do is below. The below is a sample of a nested if function.

=IF(J15="3mm",B16*K15,IF(J15="4mm",B17*K15,IF(J15="5mm",B18*K15,IF(J15="10mm",B19*K15))))
J15 is the drop down menu with all the values
B16:B19 is the value i want to receive if J15 is true
And i want to multiple B16:B19 with K15

The problem is with the bigger formula below that has about 16 nested ifs. If i write the code it works fine but the moment i close excel and open it, This is written =#VALUE! instead of my code. Below is the problematic formula

=IF(J28="1/2_Inch/1.5mm",$D$58*K28,IF(J28="3/4_Inch/1.2mm",$D$59*K28,IF(J28="3/4_Inch/1.5mm",$D$60*K28,IF(J28="1_Inch/1.2mm",$D$61*K28,IF(J28="1_Inch/1.5mm",$D$62*K28,IF(J28="1x2_Inch/1.2mm",$D$63*K28,IF(J28="1x2 Inch/1.5mm",$D$64*K28,IF(J28="1.5x1.5_Inch/1.5mm",$D$65*K28,IF(J28="1.5x1.5_Inch/2mm",$D$66*K28,IF(J28="2x2_Inch/1.5mm",$D$67*K28,IF(J28="2x2_Inch/2mm",$D$68*K28,IF(J28="3x3_Inch/3mm",$D$69*K28,IF(J28="3x3_Inch/4mm",$D$70*K28,IF(J28="4x4_Inch/4mm",$D$71*K28,IF(J28="4x4_Inch/5mm",$D$72*K28,IF(J28="4x4_Inch/6mm",$D$73*K28))))))))))))))))




Other details

1) The values such as 1/2_Inch/1.5mm & 3/4_Inch/1.2mm are all in one drop down menu
J28 is that drop down value

2) The cell D58 till D73 are the cells which have the value i want incase the J28 value is true
3) I am multiplying Cells from D:58 to D73 with K28.

Also strangely if i put this formula in windows excel it does not work at all, but it works in Mac if i put it all over again or if i just remove a few Quotation marks and put it back

I am using office 2016 mac and i also have windows 2016 excel

What I have tried:

I have tried to remove all spaces from the quotation marks but still does not work

解决方案

D


58*K28,IF(J28="3/4_Inch/1.2mm",


D


这篇关于请在excel 2016 mac中更正我的IF功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆