Term Calculations

[Oak Ville]

Hi All,

I’m looking for help on how to calculate severance in cell B23 (calculator tab).


This calculation is only required if the value in cell B5=”ON”.

The calculation is contingent on the value in cell B19.

B19 needs to be looked up in the table: Legislation tab E53:F61.

The corresponding value to be returned is: Legislation tab H53:H61.
Thank you in advance

 

[Somendra Misra]

Hi Oak,

Not sure you mentioned B19, but in file, it is mentioned ON+Service (B18), if it is a typo in your post, than try below array formula.

=IF(B5="ON",INDEX(Legislation!$H$53:$H$61,MATCH(1,(Calculator!B18>=Legislation!$E$53:$E$61)*(Calculator!B18<=Legislation!$F$53:$F$61),0)),"")

Enter with Ctrl+Shift+Enter.

Regards,

 

[Oak Ville]

Thank you very much again. This is amazing. I owe you.

 

[Somendra Misra]

Glad you solve it. One suggestion I want to make in your file on SHEET "Legislation" in column F you had left the last cells of each province empty like F10, F20 and so on, kindly fill a big number like 999999 in those cells for the complete functioning of these formulas. As in the case if the exp. or service will be more than the last cell value in column G these formulas will give an error.

Regards,

 

[Oak Ville]

Will do.

Best Regards,