nested If statement doesn't work

fhzhkunming

New Member
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.

You're welcome.

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Replies
2
Views
410

1,129,387
Messages
5,636,018
Members
416,892
Latest member
Bensch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back