nested If statement doesn't work

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
9
Office Version
  1. 365
Platform
  1. 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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Watch MrExcel Video

Forum statistics

Threads
1,129,404
Messages
5,636,095
Members
416,898
Latest member
imsorrymen

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top