more level of nesting

[clelong]

I had excell 2007 and i upgrade to 2016.
everything was working fine with 2007 and also excell 365
i have this nesting and how i can change to be able to used with excell 2016

=(IF(G3="STORAGE 1",".666666666",(IF(G3="STORAGE 2",".80",(IF(G3="TRANSPORTATION",".80",(IF(G3="STORAGE 1A",".66666666",(IF(G3="STORAGE 3",".771428571",(IF(G3="STORAGE 4",".753846154",(IF(G3="CONTAINER 20",".70",(IF(G3="CONTAINER 40",".70",(IF(G3="INCOMING 1",".90",(IF(G3="INCOMING 2",".90",(IF(G3="INCOMING 3",".90",(IF(G3="OUTGOING 1",".90",(IF(G3="OUTGOING 2",".90",(IF(G3="IMPORT FEE",".50"))))))))))))))))))))))))))))


thank you

 

[pvr928]

Hi clelong

Could you please define 'able to [be] used with excel 2016'? Your formula will function identically with both 2007 and 2016.

However, there is no value_if_false for the last logical test, so the formula will return FALSE if none of the previous logical tests are satisfied. This will be the case regardless whether 2007 or 2016 are used.

If you wish to change this, you will have to insert the value_if_false after the ".50" .

Cheers

pvr928

 

[jtakw]

Hi,

pvr is right, if your formula worked in 2007, it should work the same with 2016, so don't know what your trouble is.

You do; however, have logics in your formula that can be combined like this:

Book1
	C
1	NO MATCH
Sheet1

Cell Formulas
Range	Formula
C1	=(IF(OR(G3={"STORAGE 1","STORAGE 1A"}),".666666666",IF(OR(G3={"STORAGE 2","TRANSPORTATION"}),".80",IF(G3="STORAGE 3",".771428571",IF(G3="STORAGE 4",".753846154",IF(OR(G3={"CONTAINER 20","CONTAINER 40"}),".70",IF(OR(G3={"INCOMING 1","INCOMING 2","INCOMING 3","OUTGOING 1","OUTGOING 2"}),".90",IF(G3="IMPORT FEE",".50","NO MATCH"))))))))

Please NOTE: all the values in your formula are surrounded by quotes (i.e. ".666666666", ".80", etc.), which makes them TEXT and not real numbers.

 

[mikerickson]

with that many options, it would be better to create a VLOOKUP table than to cram it all into one (almost impossible to edit) formula.

 

[Eric W]

This kind of thing is why the LOOKUP functions exist. Deeply nesting IFs can be really confusing. Consider this alternative:

	G	H	I	J
STORAGE 1
STORAGE 2
CONTAINER 20	TRANSPORTATION
STORAGE 1A
STORAGE 3
STORAGE 4
CONTAINER 20
CONTAINER 40
INCOMING 1
INCOMING 2
INCOMING 3
OUTGOING 1
OUTGOING 2
IMPORT FEE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.666667[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.8[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0.8[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.666667[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.771429[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.753846[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.7[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.9[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.9[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.9[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0.5[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=VLOOKUP(G3,I1:J14,2,FALSE)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=INDEX({0.666666666,0.8,0.8,0.666666666,0.771428571,0.753846154,0.7,0.7,0.9,0.9,0.9,0.9,0.9,0.5},MATCH(G3,{"STORAGE 1","STORAGE 2","TRANSPORTATION","STORAGE 1A","STORAGE 3","STORAGE 4","CONTAINER 20","CONTAINER 40","INCOMING 1","INCOMING 2","INCOMING 3","OUTGOING 1","OUTGOING 2","IMPORT FEE"},0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



If you build a table like I1:J14, then you can use the simple formula in G4. Changing the values is just a matter of changing your table. The table can be hidden or on another sheet if you like.

If you really want it embedded in the formula, the G6 formula is still a bit simpler than your nested IF.

Hope this helps.

 

[clelong]

thank you