Solved

if / and statement - excel 2010

Posted on 2013-05-09
7
564 Views
Last Modified: 2013-05-21
hi experts im trying to come up with a formula to do the following'

=IF(&(C12="",c9="","",C12/$C$9+0.001)

so what im trying to do here is;
if c12 and/ or c9 = "","" otherwise c12/$c$9+0.001)

please assist with this formula
0
Comment
Question by:Frank .S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 275 total points
ID: 39154423
=IF(OR(C9="",C12=""),"",C12/$C$9+0.001)
0
 
LVL 81

Expert Comment

by:byundt
ID: 39154425
At the risk of being a trifle confusing, you can achieve the same result with:
=IF(C9&C12="","",C12/$C$9+0.001)
0
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39154506
In your Support :
=IF(AND(C9<>"",C12<>""),(($C$12/$C$9)+0.001),"")

Open in new window

Please Follow Syntax
=if(and(condition1, condition2), TrueResult, FalseResult)

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:JHalliday
ID: 39154702
The original poster is after an AND/OR statement @byundt is correct as this will trap both conditions and is also using the correct functionality in his first post but shows a compatible syntax in his second post for older version of Excel.

@jagadishdulal only shows the syntax for if both cells were empty.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39154837
Unless I am still asleep, this
=IF(C9&C12="","",C12/$C$9+0.001)
appears to be the same as
=IF(AND(C9="",C12=""),"",C12/$C$9+0.001)
rather than
=IF(OR(C9="",C12=""),"",C12/$C$9+0.001)
0
 
LVL 5

Expert Comment

by:JHalliday
ID: 39154844
@rorya lol I think its me that's half asleep yes you are correct :)
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 275 total points
ID: 39155078
With the benefit of some sleep, I now see that my two formulas may return different results--my goof! The first one is an OR and the second an AND with respect to cells C9 and C12 being blank.

If the goal is to avoid DIV/0! error value, then you need:
=IF(N($C$9)=0,"",C12/$C$9+0.001)
This formula traps C9 being either 0 or blank

If the goal is also to avoid 0 as a meaningless result, then you need:
=IF(OR(N($C$9)=0,N(C12)=0),C12/$C$9+0.001)
This formula traps both C9 and C12 as being either 0 or blank
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question