Nesting formula help

This is a discussion on Nesting formula help within the Excel Questions forums, part of the Question Forums category; I have a formula which it works fine, except if a previous cell is blank, the result is something like ...

1. Nesting formula help

I have a formula which it works fine, except if a previous cell is blank, the result is something like "-37741". Not a until I created a pivot table and those cells throw everything off. I need to add something so the result is "" or "open" instead of that way-off number. I tried adding this but error - too many arguments. I tried the Nesting article, but can't exactly figure out how to used named formulas.

Here's the formula:

=IF(K64>19,K64-8,IF(AND(K64>14,K64<19),K64-6,IF(K64>14,K64-6,IF(AND(K64>9,K64<15),K64-4,IF(AND(K64>6,K64<10),K64-2,K64)))))

2. Re: Nesting formula help

You have some redundant conditions in there... but try this:

=K64-VLOOKUP(K64,{-9.99999E-307,0;6.001,2;9.001,4;14.001,6;19.001,8},2)

3. Re: Nesting formula help

Hmm, well I have no idea how you were able to accomplish the same thing with have the characters, but it works. No, I think I see your point. Only thing, how can I get it to return something other than #N/A if value is not available?

Thanks again.

4. Re: Nesting formula help

Oops, I made a small typo on the first formula, try this one:

=K64-VLOOKUP(K64,{-9.99999E+307,0;6.001,2;9.001,4;14.001,6;19.001,8},2)

that should always produce a number.

5. Re: Nesting formula help

Well, yes that does return a number. Problem is, if there is no value in the preceding cells, K equals a way-out # like -34771, which throws my pivot tables way off. So I would like a way for the results to be "open" if it doesn't meet the other conditions.

6. Re: Nesting formula help

=IF(LEN(K64), TheFormulaIGaveYou, "open")

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•