请在excel 2016 mac中更正我的IF功能 [英] Please correct my IF function in excel 2016 mac
问题描述
我已经制作了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屋!