Forum Discussion
MULTIPLE IF CONDTITIONS OVER 64
Greetings! I have a formula with more than 64 nested items and I need to simplify it so that I can reference a labor matrix. =IF(B2=0.1,Gas!C35,IF(B2=0.2,Gas!D35,IF(B2=0.3,Gas!E35,IF(B2=0.4,G...
For multiple nested if, it is better to use IFS formula
=IFS(B2=0.1,Gas!C35,B2=0.2,Gas!D35,B2=0.3,Gas!E35,B2=0.4,Gas!F35,B2=0.5,Gas!G35,B2=0.6,Gas!H35,B2=0.7,Gas!I35,B2=0.8,Gas!J35,B2=0.9,Gas!K35,B2=1.0,Gas!B36,B2=1.1,Gas!C36,B2=1.2,Gas!D36,B2=1.3,Gas!E36,B2=1.4,Gas!F36,B2=1.5,Gas!G36,B2=1.6,Gas!H36,B2=1.7,Gas!I36,B2=1.8,Gas!J36,B2=1.9,Gas!K36,B2=2.0,Gas!B37,B2=2.1,Gas!C37,B2=2.2,Gas!D37,B2=2.3,Gas!E37,B2=2.4,Gas!F37,B2=2.5,Gas!G37,B2=2.6,Gas!H37,B2=2.7,Gas!I37,B2=2.8,Gas!J37,B2=2.9,Gas!K37,B2=3.0,Gas!B38,B2=3.1,Gas!C38,B2=3.2,Gas!D38,B2=3.3,Gas!E38,B2=3.4,Gas!F38,B2=3.5,Gas!G38,B2=3.6,Gas!H38,B2=3.7,Gas!I38,B2=3.8,Gas!J38,B2=3.9,Gas!K38,B2=4.0,Gas!B39,B2=4.1,Gas!C39,B2=4.2,Gas!D39,B2=4.3,Gas!E39,B2=4.4,Gas!F39,B2=4.5,Gas!G39,B2=4.6,Gas!H39,B2=4.7,Gas!I39,B2=4.8,Gas!J39,B2=4.9,Gas!K39,B2=5.0,Gas!B40,B2=5.1,Gas!C40,B2=5.2,Gas!D40,B2=5.3,Gas!E40,B2=5.4,Gas!F40,B2=5.5,Gas!G40,B2=5.6,Gas!H40,B2=5.7,Gas!I40,B2=5.8,Gas!J40,B2=5.9,Gas!K40,B2=6.0,Gas!B41,B2=6.1,Gas!C41,B2=6.2,Gas!D41,B2=6.3,Gas!E41,B2=6.4,Gas!F41,B2=6.5,Gas!G41,B2=6.6,Gas!H41,B2=6.7,Gas!I41,B2=6.8,Gas!J41,B2=6.9,Gas!K41,B2=7.0,Gas!B42,B2=7.1,Gas!C42,B2=7.2,Gas!D42,B2=7.3,Gas!E42,B2=7.4,Gas!F42,B2=7.5,Gas!G42,B2=7.6,Gas!H42,B2=7.7,Gas!I42,B2=7.8,Gas!J42,B2=7.9,Gas!K42,B2=8.0,Gas!B43,B2=8.1,Gas!C43,B2=8.2,Gas!D43,B2=8.3,Gas!E43,B2=8.4,Gas!F43,B2=8.5,Gas!G43,B2=8.6,Gas!H43,B2=8.7,Gas!I43,B2=8.8,Gas!J43,B2=8.9,Gas!K43,B2=9.0,Gas!B44,B2=9.1,Gas!C44,B2=9.2,Gas!D44,B2=9.3,Gas!E44,B2=9.4,Gas!F44,B2=9.5,Gas!G44,B2=9.6,Gas!H44,B2=9.7,Gas!I44,B2=9.8,Gas!J44,B2=9.9,Gas!K44,B2=10.0,Gas!B45,B2=10.1,Gas!C45,B2=10.2,Gas!D45,B2=10.3,Gas!E45,B2=10.4,Gas!F45,B2=10.5,Gas!G45,B2=10.6,Gas!H45,B2=10.7,Gas!I45,B2=10.8,Gas!J45,B2=10.9,Gas!K45,B2=11.0,Gas!B46,B2=11.1,Gas!C46,B2=11.2,Gas!D46,B2=11.3,Gas!E46,B2=11.4,Gas!F46,B2=11.5,Gas!G46,B2=11.6,Gas!H46,B2=11.7,Gas!I46,B2=11.8,Gas!J46,B2=11.9,Gas!K46,B2=12.0,Gas!B47,B2=12.1,Gas!C47,B2=12.2,Gas!D47,B2=12.3,Gas!E47,B2=12.4,Gas!F47,B2=12.5,Gas!G47,B2=12.6,Gas!H47,B2=12.7,Gas!I47,B2=12.8,Gas!J47,B2=12.9,Gas!K47,B2=13.0,Gas!B48,B2=13.1,Gas!C48,B2=13.2,Gas!D48,B2=13.3,Gas!E48,B2=13.4,Gas!F48,B2=13.5,Gas!G48,B2=13.6,Gas!H48,B2=13.7,Gas!I48,B2=13.8,Gas!J48,B2=13.9,Gas!K48,B2=14.0,Gas!B49,B2=14.1,Gas!C49,B2=14.2,Gas!D49,B2=14.3,Gas!E49,B2=14.4,Gas!F49,B2=14.5,Gas!G49,B2=14.6,Gas!H49,B2=14.7,Gas!I49,B2=14.8,Gas!J49,B2=14.9,Gas!K49,B2=15.0,Gas!B50,B2=15.1,Gas!C50,B2=15.2,Gas!D50,B2=15.3,Gas!E50,B2=15.4,Gas!F50,B2=15.5,Gas!G50,B2=15.6,Gas!H50,B2=15.7,Gas!I50,B2=15.8,Gas!J50,B2=15.9,Gas!K50,TRUE,0)
Excel provides multiple solution for similar one issue.
=IFS(B2=0.1,Gas!C35,B2=0.2,Gas!D35,B2=0.3,Gas!E35,B2=0.4,Gas!F35,B2=0.5,Gas!G35,B2=0.6,Gas!H35,B2=0.7,Gas!I35,B2=0.8,Gas!J35,B2=0.9,Gas!K35,B2=1.0,Gas!B36,B2=1.1,Gas!C36,B2=1.2,Gas!D36,B2=1.3,Gas!E36,B2=1.4,Gas!F36,B2=1.5,Gas!G36,B2=1.6,Gas!H36,B2=1.7,Gas!I36,B2=1.8,Gas!J36,B2=1.9,Gas!K36,B2=2.0,Gas!B37,B2=2.1,Gas!C37,B2=2.2,Gas!D37,B2=2.3,Gas!E37,B2=2.4,Gas!F37,B2=2.5,Gas!G37,B2=2.6,Gas!H37,B2=2.7,Gas!I37,B2=2.8,Gas!J37,B2=2.9,Gas!K37,B2=3.0,Gas!B38,B2=3.1,Gas!C38,B2=3.2,Gas!D38,B2=3.3,Gas!E38,B2=3.4,Gas!F38,B2=3.5,Gas!G38,B2=3.6,Gas!H38,B2=3.7,Gas!I38,B2=3.8,Gas!J38,B2=3.9,Gas!K38,B2=4.0,Gas!B39,B2=4.1,Gas!C39,B2=4.2,Gas!D39,B2=4.3,Gas!E39,B2=4.4,Gas!F39,B2=4.5,Gas!G39,B2=4.6,Gas!H39,B2=4.7,Gas!I39,B2=4.8,Gas!J39,B2=4.9,Gas!K39,B2=5.0,Gas!B40,B2=5.1,Gas!C40,B2=5.2,Gas!D40,B2=5.3,Gas!E40,B2=5.4,Gas!F40,B2=5.5,Gas!G40,B2=5.6,Gas!H40,B2=5.7,Gas!I40,B2=5.8,Gas!J40,B2=5.9,Gas!K40,B2=6.0,Gas!B41,B2=6.1,Gas!C41,B2=6.2,Gas!D41,B2=6.3,Gas!E41,B2=6.4,Gas!F41,B2=6.5,Gas!G41,B2=6.6,Gas!H41,B2=6.7,Gas!I41,B2=6.8,Gas!J41,B2=6.9,Gas!K41,B2=7.0,Gas!B42,B2=7.1,Gas!C42,B2=7.2,Gas!D42,B2=7.3,Gas!E42,B2=7.4,Gas!F42,B2=7.5,Gas!G42,B2=7.6,Gas!H42,B2=7.7,Gas!I42,B2=7.8,Gas!J42,B2=7.9,Gas!K42,B2=8.0,Gas!B43,B2=8.1,Gas!C43,B2=8.2,Gas!D43,B2=8.3,Gas!E43,B2=8.4,Gas!F43,B2=8.5,Gas!G43,B2=8.6,Gas!H43,B2=8.7,Gas!I43,B2=8.8,Gas!J43,B2=8.9,Gas!K43,B2=9.0,Gas!B44,B2=9.1,Gas!C44,B2=9.2,Gas!D44,B2=9.3,Gas!E44,B2=9.4,Gas!F44,B2=9.5,Gas!G44,B2=9.6,Gas!H44,B2=9.7,Gas!I44,B2=9.8,Gas!J44,B2=9.9,Gas!K44,B2=10.0,Gas!B45,B2=10.1,Gas!C45,B2=10.2,Gas!D45,B2=10.3,Gas!E45,B2=10.4,Gas!F45,B2=10.5,Gas!G45,B2=10.6,Gas!H45,B2=10.7,Gas!I45,B2=10.8,Gas!J45,B2=10.9,Gas!K45,B2=11.0,Gas!B46,B2=11.1,Gas!C46,B2=11.2,Gas!D46,B2=11.3,Gas!E46,B2=11.4,Gas!F46,B2=11.5,Gas!G46,B2=11.6,Gas!H46,B2=11.7,Gas!I46,B2=11.8,Gas!J46,B2=11.9,Gas!K46,B2=12.0,Gas!B47,B2=12.1,Gas!C47,B2=12.2,Gas!D47,B2=12.3,Gas!E47,B2=12.4,Gas!F47,B2=12.5,Gas!G47,B2=12.6,Gas!H47,B2=12.7,Gas!I47,B2=12.8,Gas!J47,B2=12.9,Gas!K47,B2=13.0,Gas!B48,B2=13.1,Gas!C48,B2=13.2,Gas!D48,B2=13.3,Gas!E48,B2=13.4,Gas!F48,B2=13.5,Gas!G48,B2=13.6,Gas!H48,B2=13.7,Gas!I48,B2=13.8,Gas!J48,B2=13.9,Gas!K48,B2=14.0,Gas!B49,B2=14.1,Gas!C49,B2=14.2,Gas!D49,B2=14.3,Gas!E49,B2=14.4,Gas!F49,B2=14.5,Gas!G49,B2=14.6,Gas!H49,B2=14.7,Gas!I49,B2=14.8,Gas!J49,B2=14.9,Gas!K49,B2=15.0,Gas!B50,B2=15.1,Gas!C50,B2=15.2,Gas!D50,B2=15.3,Gas!E50,B2=15.4,Gas!F50,B2=15.5,Gas!G50,B2=15.6,Gas!H50,B2=15.7,Gas!I50,B2=15.8,Gas!J50,B2=15.9,Gas!K50,TRUE,0)
Excel provides multiple solution for similar one issue.