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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I'm not seeing a problem with your formula, except your description is testing for 0 in B9 & C14, but your formula is testing for Blank ""
so maybe just change that part?
I also cleaned up the formula a bit:

Excel Formula:
=IF(B9<>0,B9,IF(C14<>0, C14,IF(OR(C20={"10-146","10-110","Extremely weak"}),50,IF(OR(C20={"147-620","111-1,315","Weak"}),500,IF(OR(C20={"630-2,690","1,316-15,500","Moderate"}),5000,
IF(OR(C20={"2,700-11,500","15,600-184,000","Moderately strong"}),50000,IF(OR(C20={"11,600-49,700","185,000-2,190,000","Strong"}),500000,IF(OR(C20={"49,800-213,000","2,200,000+","Extremely strong"}),2200000,IF(C20="214,000-916,000",10000000,IF(C20="917,000-3,930,000",40000000,IF(C20="3,940,000+",50000000)))))))))))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Are both B9 & C14 both blank or 0?
Also you could clean the formula up considerably if you create a lookup table for the C20 values.
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi jtakw,

Thanks for your quick response. But your suggestion is not working.

I have a change event via VBA code. Once I change or select an option from C20, the content of B9 and C14 will be cleared out and that's why I put "", empty, to test. But before I go to C20, I may have values in either B9 or C14. If I get a value from either cells, I don't need to go to C20.
As for XL2BB coding format, I am using a computer belonging to my organization and don't have admin privileges to make any change to the computer. I tried to install the Add-in, but it's not available on my computer. This is my first time to post. I will look around and see if I can find a way to do it. Sorry for the inconvenience.

Thanks again.

Frank
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Fluff,
Thanks for your response. Yes, if I need to go C20, both B9 & C14 are blank.
Thanks,

Frank
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
Good evening.

Yours is a classic case for using the VLOOKUP function.
A separate table always makes the job easier.

It is always easy to find values and references when maintenance is required.

You can still use the id column itself to generate the list for data validation.

There follows a modest contribution to this case.

it is only the beginning of the table to complete it with the other values.

D20 -->
Excel Formula:
=IF(B9<>0, B9, IF(C14<>0, C14, VLOOKUP(C20,G3:H12,2,FALSE)))

I hope it helps.
 

Attachments

  • Lookup_table.PNG
    Lookup_table.PNG
    7.8 KB · Views: 1

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Marcílio_Lobão,​


Thanks for your suggestion. C20 is a dependent dropdown list, and its contents depends on another dropdown list in B20. Can I still use VLOOKUP? I am not familiar with VLOOKUP.

Thanks
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
fhzhkunming, Good evening.

Without being able to see the assembly of your spreadsheet, it is very difficult to make efficient indications.

Maybe if you save your spreadsheet on some free site, just put the link here so we can see the logic in the spreadsheet.

Is there any place where the texts in the list that will appear in C20 are written together with the values?

The VLOOKUP function is very simple but it is powerful.

I hope I can help you.
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi Members,

Thank you very much for your suggestions and comments. The ideas from you guys give me inspiration to solve the problem.
There is nothing wrong with my IF statement. However, C14 contains a formula and depends the input of C12, which contains a dropdown list with 2 options. I need to chose one specific option, default option, to make the IF statement in D20 working. To achieve this, I added one line in my Worksheet_Change VBA code, which changes C12 to the default option once any changes are made to C20. Problem solved!
I will try to find a way to post sample spreadsheet next time.
Thank you all!

F
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,468
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,014
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.
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