Multiple IF's

[Thomas Kuriakose]

Respected Sirs,

Kindly find attached a workbook, where I have used multiple ifs to arrive at the percentage column F, but I am not able to get the result for 100.

Conditions:
1. Column E>0 and <=2 - Result should be 20
2. Column E>2 and <=3 - Result should be 40
3. Column E>3 and <=10 - Result should be 60
4. Column E>10 and Column B>0 - Result should be 80
5. Column E>10 and Column B=0 or blank - Result should be 100

Kindly let me know the error in the multiple if used.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[Chihiro]

That's because of #DIV/0! error in Column E.

Just nest Column E formula in IFERROR().
=IFERROR(C2/D2,"")

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for the support on this problem.

Much appreciated.

with regards,
thomas

 

[bosco_yip]

Try,

1] In "Unit" E2, formula copy down :

=IF(D2>0,C2/D2,0)

2] In "%" F2, formula copy down :

=IF(B2=0,100,IF(E2<=2,20,IF(E2<=3,40,IF(E2<=10,60,IF(E2>10,80)))))

2a] Alternative formula for "%" G2, formula copy down :

=IF(B2=0,100,SUM((E2>={0;2.01;3.01;10.01})*20))

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this option provided.

Much appreciated.

with regards,
thomas