fhzhkunming
New Member
- Joined
- Mar 4, 2021
- Messages
- 30
- Office Version
- 365
- Platform
- Windows
I want to get a value in Cell D20 based on the value of B9, C14 or C20. If B9<> 0, I want the value in B9 to be shown in D20 and don't worry about the rest; otherwise, if B9=0, then check C14; if C14 <>0, value of C14 goes to D20; otherwise, check C20. C20 has a dropdown list, each item has a corresponding value that should go to D20. Here is my nested if statement:
=IF(B9<>"",B9,
IF(C14<>"", C14,
IF(OR(C20="10-146",C20="10-110",C20="Extremely weak"),50,
IF(OR(C20="147-620",C20="111-1,315",C20="Weak"),500,
IF(OR(C20="630-2,690",C20="1,316-15,500",C20="Moderate"),5000,
IF(OR(C20="2,700-11,500",C20="15,600-184,000",C20="Moderately strong"),50000,
IF(OR(C20="11,600-49,700",C20="185,000-2,190,000",C20="Strong"),500000,
IF(OR(C20="49,800-213,000",C20="2,200,000+",C20="Extremely strong"),2200000,
IF(C20="214,000-916,000",10000000,
IF(C20="917,000-3,930,000",40000000,
IF(C20="3,940,000+",50000000)))))))))))
I couldn't get a value from dropdown in C20 to D20 if both B9 and C14 are 0. But if I took out "IF(C14<>"", C14," this part, the nested if statement seems to work.
I would appreciate it very much if anyone could help me out.
Thanks in advance
=IF(B9<>"",B9,
IF(C14<>"", C14,
IF(OR(C20="10-146",C20="10-110",C20="Extremely weak"),50,
IF(OR(C20="147-620",C20="111-1,315",C20="Weak"),500,
IF(OR(C20="630-2,690",C20="1,316-15,500",C20="Moderate"),5000,
IF(OR(C20="2,700-11,500",C20="15,600-184,000",C20="Moderately strong"),50000,
IF(OR(C20="11,600-49,700",C20="185,000-2,190,000",C20="Strong"),500000,
IF(OR(C20="49,800-213,000",C20="2,200,000+",C20="Extremely strong"),2200000,
IF(C20="214,000-916,000",10000000,
IF(C20="917,000-3,930,000",40000000,
IF(C20="3,940,000+",50000000)))))))))))
I couldn't get a value from dropdown in C20 to D20 if both B9 and C14 are 0. But if I took out "IF(C14<>"", C14," this part, the nested if statement seems to work.
I would appreciate it very much if anyone could help me out.
Thanks in advance