Rewrite IFS formula - Or create addin to be able to use

[megadentalda]

Hello,
I have the latest version of excel and created a sheet with a "IFS" formula in one of the sheets. It is a long formula with many if statements. I copied sheet over to another computer only to find out that it was using a older version of office and does not have the functionality. So was wondering if anyone can help me to replace my ifs formula or create a addin vba code which will do the same thing?

Here is the code i am talking about:

=IF(E4< d4,ifs(c4=30,30+(e4-d4),c4=20,30+(e4-d4),c4=10,60+(e4-d4),c4=5,60+(e4-d4),c4=3,100+(e4-d4),c4=2,150+(e4-d4),c4=1,300+(e4-d4)),e4-d4)

I am using this formula in 40 lines a sheet and have 30 sheets in workbook. so if vba the code must be "if(e?< d?,ifs(c?.....)

it is basically a function saying if E4 is less than D4 then depending on what value is in column C I would need it to calculate the number in a different way.

Any help you can give me would be greatly appreciated!!!

Thanks

 

[Fluff]

How about
=IF(E4< D4,IF(OR(C4={30,20}),30+(E4-D4),IF(OR(C4={10,5}),60+(E4-D4),IF(C4=3,100+(E4-D4),IF(C4=2,150+(E4-D4),IF(C4=1,300+(E4-D4)))))),E4-D4)

 

[megadentalda]

thank you!! that worked perfectly!!

How about
=IF(E4< D4,IF(OR(C4={30,20}),30+(E4-D4),IF(OR(C4={10,5}),60+(E4-D4),IF(C4=3,100+(E4-D4),IF(C4=2,150+(E4-D4),IF(C4=1,300+(E4-D4)))))),E4-D4)

 

[Fluff]

You're welcome & thanks for the feedback.

I would also recommend not using the new IFS function, partly because of backwards compatibility, but also because it evaluates everything & can therefore slow down your sheet.

 

[DanteAmor]

If one of these values (1;2;3;5;10;20;30) ​​always goes in cell C4, then it can be like this:



=IF(E4 < D4,LOOKUP(C4,{1,2,3,5,10,20,30},{300,150,100,60,60,30,30}))+E4-D4




Otherwise, try this:

=IF(E4 < D4,IF(OR(C4={1,2,3,5,10,20,30}),LOOKUP(C4,{1,2,3,5,10,20,30},{300,150,100,60,60,30,30})+(E4-D4),"Incorrect value"),E4-D4)