IFS Statement

[tennisbuck12]

Team,

I have a big issue/need to complete a DB by AM Thursday. Would someone be able to help me with this IFS statement and why it returns #name .

IFS(F5=0, 150,F5<=1, (1-F5)/1*25+125,F5<=3, (3-F5)/2*25+100,F5<=5, (5-F5)/2*50+50,F5>5,0)

When I recieved the Formula it was like this:

=_xlfn.IFS(F5=0, 150,F5<=1, (1-F5)/1*25+125,F5<=3, (3-F5)/2*25+100,F5<=5, (5-F5)/2*50+50,F5>5,0)

It worked fine.

 

[jmacleary]

Re: IFS Statement HELP

Hi tennisbuck

The IFS function is an office365 (office2016) function. If you save the workbook as any previous version, then the _xlfn gets prefixed as the earlier versions dont know what the function is. You say it worked fine - which version of excel did it work in?

 

[tennisbuck12]

Re: IFS Statement HELP

Hi tennisbuck

The IFS function is an office365 (office2016) function. If you save the workbook as any previous version, then the _xlfn gets prefixed as the earlier versions dont know what the function is. You say it worked fine - which version of excel did it work in?

I use 2013 and I believe the person sent this to me uses 2016. How would i rewrite the formula?

 

[AlanY]

Re: IFS Statement HELP

try

=IF(F5=0, 150,IF(F5<=1, (1-F5)/1*25+125,IF(F5<=3, (3-F5)/2*25+100,IF(F5<=5, (5-F5)/2*50+50,0))))

 

[tennisbuck12]

Re: IFS Statement HELP

try

=IF(F5=0, 150,IF(F5<=1, (1-F5)/1*25+125,IF(F5<=3, (3-F5)/2*25+100,IF(F5<=5, (5-F5)/2*50+50,0))))

Works Perfect! Would you be able to help me with the 4 others

=_xlfn.IFS(I2>=99, 150,I2>=95, (I2-95)/4*50+100,I2>=90, (I2-90)/5*25+75,I2>=80, (I2-80)/10*25+50,I2<80,0)

=_xlfn.IFS(L2>=99, 150,L2>=95, (L2-95)/4*25+125,L2>=90, (L2-90)/5*25+100,L2>=70, (L2-70)/20*50+50,L2<70,0)

=_xlfn.IFS(O2<=30, 150,O2<=70, (70-O2)/40*100+50,O2>70,0)

=_xlfn.IFS(R2<=80, 150,R2<=100, (100-R2)/20*50+100,R2<=120, (120-R2)/20*25+75,R2<=150, (150-R2)/30*25+50,R2>100,0)