I use Excel IFS function to add *
The working function is:
=IF(G5="","",FIXED(G5,1)&IFS(ABS(G6)>=2.575,"***",ABS(G6)>=1.96,"**",ABS(G6)>=1.645,"*",ABS(G6)<1.645,""))
Since I see ABS(G6)<1.645 is redundant so I decided to drop it
=IF(G5="","",FIXED(G5,1)&IFS(ABS(G6)>=2.575,"***",ABS(G6)>=1.96,"**",ABS(G6)>=1.645,"*"))
But the formula run into issue return #N/A
I hope someone can explain for me why removing part cause the issue?
You need to provide the "else" branch for IFS:
=IF(G5="",
"",
FIXED(G5,1)&IFS(
ABS(G6)>=2.575,"***",
ABS(G6)>=1.96,"**",
ABS(G6)>=1.645,"*",
TRUE,""
)
)
Read Remarks in IFS function:
To specify a default result, enter TRUE for your final logical_test argument. If none of the other conditions are met, the corresponding value will be returned.
So, you've got '#N/A' due to ABS(G6)<1.645
and there is no such condition in the list.
Also, you can suppress '#N/A' by another way:
=IF(G5="",
"",
FIXED(G5,1)&IFNA(
IFS(
ABS(G6)>=2.575,"***",
ABS(G6)>=1.96,"**",
ABS(G6)>=1.645,"*"
),
""
)
)