ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   nested function limitation (https://www.excelbanter.com/excel-discussion-misc-queries/211648-nested-function-limitation.html)

ela

nested function limitation
 
yesterday Bob taught me to use function sumif to help count:

decision Base <50 50-60 60-70 70
FN 20.632 1 0 0 0
FN 34.492 1 0 0 0
FN 37.398 1 0 0 0
FP 65.422 0 0 1 0
FP 161.897 0 0 0 1
FP 102.764 0 0 0 1
FP 90.76 0 0 0 1
FN 10.738 1 0 0 0
FP 151.7 0 0 0 1

to derive the statistics just below

decision <50 50-60 60-70 70
FN 4 0 0 0
FP 0 0 1 4

when I tried to add one more group (column),

e.g.
TRUE FN 20.632 1 0 0 0
FALSE FN 34.492 1 0 0 0

I use the following formula to derive the class (total 6, i.e. TRUE FN, FP,
FALSE FN, FP and null FN, FP):

I found the if cannot let me write a "long" one because the level is limited
to 7
=IF(M2="TRUE",IF(N2="FN",1,IF(N2="FP",2,99)))

anybody has a solution? Thanks a lot!!!!!



Daniel.C[_3_]

nested function limitation
 
You can use :
=(M2="TRUE")*(N2="FN")+(M2="TRUE")*(N2="FP")*2
to avoid nested ifs.
HTH
Daniel

yesterday Bob taught me to use function sumif to help count:

decision Base <50 50-60 60-70 70
FN 20.632 1 0 0 0
FN 34.492 1 0 0 0
FN 37.398 1 0 0 0
FP 65.422 0 0 1 0
FP 161.897 0 0 0 1
FP 102.764 0 0 0 1
FP 90.76 0 0 0 1
FN 10.738 1 0 0 0
FP 151.7 0 0 0 1

to derive the statistics just below

decision <50 50-60 60-70 70
FN 4 0 0 0
FP 0 0 1 4

when I tried to add one more group (column),

e.g.
TRUE FN 20.632 1 0 0 0
FALSE FN 34.492 1 0 0 0

I use the following formula to derive the class (total 6, i.e. TRUE FN, FP,
FALSE FN, FP and null FN, FP):

I found the if cannot let me write a "long" one because the level is limited
to 7
=IF(M2="TRUE",IF(N2="FN",1,IF(N2="FP",2,99)))

anybody has a solution? Thanks a lot!!!!!





All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com