Nested IF(OR(AND) statements in Excel 1

[sterlecki]

In Excel
The following formula works fine except for one thing:

=IF(A12="","",SQRT(IF(OR(A12-200<Tw,A12>Kmv),0.15,0.1)/(H12*G12^2)))

If the cell containing Kmv is blank, null or less than Tw... Then the formula uses (.15) when it should use (0.1)

If the cell containing Kmv has a value greater than A12 everything is fine.

I would like to combine this formula with something to test that (A12>Kmv and Not(IsBlank(Kmv))
IsNull
To summarize:
If (A12-200)< Tw [True] use .15
OR
(A12>Kmv and Not(IsBlank(Kmv))
[True] and [False] use .10
[True] and [True] use .15

Suggestions Please! I've tried many permutations but so far all have given up errors. I was able to write this in Access sucessfully but not in Excel.

 

[01Patrik]

You wrote it yourself:

=IF(A12="","",SQRT(IF(and(A12>Kmv,Not(IsBlank(Kmv)),not(isnull(kmv))),if(
OR(A12-200<Tw,A12>Kmv),0.15,0.1),0.1)/(H12*G12^2)))

Only written here, not tested, I might add. I've only included another IF:

SQRT(IF(and(yourcriterias)),yourIF,0,1)/(H12...))


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[sterlecki]

No that didn't work at all. No system or syntax errors just #####. I will play with it alittle but thanks for trying.

 

[AnotherHiggins]

What does the error ##### mean?
A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date and/or time formula that produces a negative result.
[So either]
Increase the width of the column
...
Apply a different number format

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[123FakeSt]

You can only nest a certain number of times depending on the version of excel.

Perhaps your exceeding this limit?

The early bird gets the worm, but the second mouse gets the cheese.

 

[TonyJollans]

Hi sterlecki,

Your post is not entirely clear:

If the cell containing Kmv is blank, null or less than Tw... Then the formula uses (.15) when it should use (0.1)

(my emphasis). You are not checking Kmv against Tw, or asking for it to be checked.

Ignoring that aspect, I think this is what you want:

[tt]IF([blue]OR([red](A12-200)<Tw[/red],[green]AND(A12>Kmv,=ISBLANK(Kmv)=FALSE)[/green])[/blue],0.15,0.10)[/tt]

(along with the SQRT and the rest of it of course)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[sterlecki]

Thanks Tony!

The successful ended up being

=IF(A12="","",SQRT(IF(OR(A12-200<Tw,AND(A12>Kmv,ISBLANK(Kmv)=FALSE)),0.15,0.1)/(H12*G12^2)))

per your example A Star for you