This works:
=(M2*N2*3.14)+(O2*D2*3.14)+IF((C2="P"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF((C2="PT"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"})+LOOKUP(G2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+
IF(AND(C2="PT",F2="S"),(G2*E2*3.14*0.15)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="L"),(G2*E2*3.14*0.25)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="C"),(G2*E2*3.14*0.5)+(G2*E2*3.14*2),0)+
IF(AND(C2="PT",F2="SH"),(G2*E2*3.14*0.75)+(G2*E2*3.14*2),0),
IF((C2="ELB"),(D2*H2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF((C2="RED"),(D2*3.14)+(I2*3.14)+LOOKUP(D2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+LOOKUP(I2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"}),
IF((C2="OFF"),(D2*2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),0)))))
Might not be perfect but it works and does what I want it to do.
If I try to add this, which works spectacularly alone, I get error codes every which way I turn.
=IF(AND(C3="DMP",D3>3,D3<15),8.75,0)
Like this (second row):
=(M2*N2*3.14)+(O2*D2*3.14)+IF((C2="P"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF(AND(C2="DMP",D2>3,D2<15),8.75,0),
IF((C2="PT"),LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"})+LOOKUP(G2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+
IF(AND(C2="PT",F2="S"),(G2*E2*3.14*0.15)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="L"),(G2*E2*3.14*0.25)+(G2*E2*3.14),0)+
IF(AND(C2="PT",F2="C"),(G2*E2*3.14*0.5)+(G2*E2*3.14*2),0)+
IF(AND(C2="PT",F2="SH"),(G2*E2*3.14*0.75)+(G2*E2*3.14*2),0),
IF((C2="ELB"),(D2*H2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),
IF((C2="RED"),(D2*3.14)+(I2*3.14)+LOOKUP(D2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"})+LOOKUP(I2,{0,0;31,"20";42,"23";54,"25";68,"28";86,"30";106,"33"}),
IF((C2="OFF"),(D2*2*3.14)+LOOKUP(D2,{0,0;31,"40";42,"46";54,"50";68,"56";86,"60";106,"66"}),0)))))
I keep getting either "too many arguments" or #value errors. It's either this formula I invented is just too intricate for excel to handle and I'm a genius (joke)/excel is dumb or it's something basic and silly that I'm overlooking and I'm a bonehead. I know I'm missing some "0"'s for false returns but I've tried throwing a bunch in and get the same errors and it still doesn't explain why it works one way and not the other. I'd like to throw in about 3-5 more IF functions into this, hopefully I'm not at a limit and therefor it's the previously explained scenario where excel is dumb.
Bookmarks