Solved

if / and statement - excel 2010

Posted on 2013-05-09
7
555 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:FrankSasso
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

839 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