Appraisal using IFs Function

[Komez4]

Hi All,
An appraisal spreadsheet was sent to me using IFs function,=@IFS(G2<=2,5,G2=3,4,G2<=5,3,G2<=7,2,G2<=10,1,G2>10,0) i couldn't open it in my excel 2016. Please is there a way to view it on my excel 2016 or use alternative functions? Your response is eagerly expected

 

[Komez4]

The last two columns where IFs were used is displaying an error

 

[bosco_yip]

The alternative for :

=@IFS(G2<=2,5,G2=3,4,G2<=5,3,G2<=7,2,G2<=10,1,G2>10,0)

is equal to >>

=6-MATCH(G2,{0,3,4,6,8,11})

for all Excel versions, and

both formula return the same result

Regards

 

[Komez4]

Thank you for your prompt response. I will try it but incase I couldn't achieve it,bi will come calling again. Thank God for a genuine site like this.

 

[Komez4]

hi All,
Please find attached the appraisal form in spreadsheet, I was using my phone initially and I couldn't upload. Kindly, assist with the alternate formula to use in the last 2 columns of the attached file. Thank you so much in anticipation.

 

[Komez4]

=_xlfn.IFS(D2="VERY HIGH",(J2*5)/25,D2="HIGH",(J2*4)/20,D2="MODERATE",(J2*3)/15, D2="ROUTINE",(J2*2)/10, D2="NO IMPACT",(J2*1)/5)
Please how do I use MATCH function?

 

[bosco_yip]

Your formulas made adjusting as in :

1] In G2, formula copied down:

=IF(B2="","",IF(I2>0, I2-E2, TODAY()-E2))

2] In J2, formula copied down:

=IF(B2="","",6-MATCH(G2,{0,3,4,6,8,11}))

3] In K2, formula copied down:

=IF(B2="","",J2*INDEX({"0 5/25";"0 4/20";"0 3/15";"0 2/10";"0 1/5"},MATCH(D2,{"VERY HIGH";"HIGH";"MODERATE";"ROUTINE";"NO IMPACT"},0)))

 

[Komez4]

A very big THANK YOU.... You are the bomb. Thank you for making out time to resolve it.