IF function and drop-down lists

**ouaga** · 09-23-2009, 09:21 AM

Hi,

I've got an IF statement that's based around what's chosen from a drop-down list. The problem is I'm using "" to stand for nothing being chosen in the cell in question, and this is returning an error message and highlighting the '""' (that looks confusing).

I've tried checking and unchecking 'ignore blanks' in data validation but nothing much has happened; I have now turned to the internet for help.

Here's an example of the formula I'm using:

=IF((AND(C40=A,D40="")),C9,(IF((OR(C40=A,D40=DA)),C9/2,"")))

The problematic "" is the first one.

Thank you so much for any help.

**NBVC** · 09-23-2009, 09:24 AM

are A and DA named cells or text strings.. if text strings they should be enclosed in quotes, other than that, the formula looks ok.

is there a formula in D40? What error are you getting?

**ouaga** · 09-23-2009, 10:01 AM

They're cells. I can't remember the error message, I restarted excel and excel started accepting the formula. I really have no idea what was going on.

Thanks though! :P

I do have one other problem, though. I have this formula, and others, working whenever they damn please. Is there any way to make it so that the only function when a certain cell (say, B2) shows a positive number? Conditional formatting isn't helping.

**NBVC** · 09-23-2009, 10:06 AM

Not sure what you mean...

you can either turn off Automatic calculations and only have formulas perform calculations when you press F9...

or. you can alter the formulas to include an IF statement stating something like this:

=IF(B2>0,do_this,do_that)

**ouaga** · 09-23-2009, 10:19 AM

That IF statement idea sounds good; would this be right?:

changing

=IF(COUNTA(C40:E40),(COUNTIF(C40:E40,D140)>0)/COUNTA(C40:E40),0)

to

=IF((C9>0),IF(COUNTA(C40:E40),(COUNTIF(C40:E40,D141)>0)/COUNTA(C40:E40),0),"")

**NBVC** · 09-23-2009, 10:30 AM

Yes, that would return a blank cell if C9 is less than or equal to 0.

**ouaga** · 09-23-2009, 10:31 AM

Awesome. Sorry, I'm not very good at embedding functions. Thank you!

**NBVC** · 09-23-2009, 10:44 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save