Cell isn't reading as truly blank, & Nested IF issue

**Lizabeta** · 05-15-2009, 02:46 PM

Cell A1 = IF(ISBLANK(C1), H1/B1, C1/B1)
Cell C1 refers to another workbook.

But since C1 really reads:
='Y:\MP Workbooks\[Day planning.xls]Menu Planning'!$F10
then it isn't finding it as "ISBLANK" and won't calculate correctly.

I've also tried putting the long out-of-workbook reference in another column and only referencing it in C1 only as =R1 (which works as a get-around for another issue I was having with a different formula) but that's not working either...

How can I get the IF statement to say:

If C1 looks blank (returns a value from 'Day planning.xls' as 0, or returns no value) then use H1/B1. If C1 returns any value other than 0 or nothing from the 'Day planning.xls' then use C1/B1.

It's also nested:
=IF(ISERR(C1/B1),"", IF(ISBLANK(C1), H1/B1, C1/B1))

Is it that the nesting that is causing the issue?

**Special-K** · 05-15-2009, 02:53 PM

How about

Please Login or Register  to view this content.

**NBVC** · 05-15-2009, 02:57 PM

Maybe?

=IF(OR(C1=0,C1=""), H1/B1, C1/B1))

**Lizabeta** · 05-15-2009, 04:43 PM

=IF(ISERR(OR(G8=0, G8=""), H8/D8, R8/D8),"", IF(OR(G8=0,G8=""),H8/D8,R8/D8))

So... If I'm getting this right, what this should do is:

IF: there is an error figuring out (OR(G8=0, G8="", H8/D8, R8/D8),
(true)THEN: "" leave it blank
but if there is no error:
(false) If G8 is 0 or blank
(true) use H8/D8
(false) use R8/D8

But it excel won't let me use that formula... I've done something wrong. It highlights it:

=IF(ISERR(OR(G8=0, G8=""), H8/D8, R8/D8),"", IF(OR(G8=0,G8=""),H8/D8,R8/D8))

**Lizabeta** · 05-15-2009, 04:54 PM

=IF(ISERR(IF(ISNUMBER(R8),R8/D8,H8/D8)), "", IF(ISNUMBER(R8),R8/D8,H8/D8))

This one doesn't give me an error, however, on a few seemingly random lines it stops calculating for false in the second have of the formula, where it had calculated correctly using the OR formula above.

What I'm trying to do is suppress the #Value that occurs when there are blank lines.

**Lizabeta** · 05-15-2009, 06:33 PM

I couldn't get the ISNUMBER one to calculate...
This finally works...

=IF(ISERR(OR(H8/D8, R8/D8)),"", IF(OR(G8=0,G8=""),H8/D8,R8/D8))

used to be:

=IF(ISERR(OR(G8=0, G8=""), H8/D8, R8/D8),"", IF(OR(G8=0,G8=""),H8/D8,R8/D8))

So, I removed the (G8=0, G8="") part ffrom the first part of the equation and it solved correctly.

**shg** · 05-15-2009, 08:01 PM

How about just

=IF(N(C1), C1, H1) / B1

**Lizabeta** · 05-16-2009, 01:19 AM

What is the "n" part of that?

**shg** · 05-16-2009, 11:07 AM

It converts its argument to a number if it can; N("123") = 123. But unlike VALUE, N("text") = 0.

It's a little elusive to find in help. Easiest is to put the cursor inside the parens in the formula bar, click the fx button, and then click Help on this function in the Function Arguments dialog

**Lizabeta** · 05-16-2009, 12:05 PM

Spiffy... I'll try that when I get back to work on Monday! I'm having issues with the formula above, but it could be networking issues our computers are experiencing. They won't calculate at all... but then, a few of them suddenly calculated on their own. Seriously, my boss was looking over my shoulder and like, three of them randomly filled in on their own. And there is no difference between the formulas below or above them.... So, I'm hoping its a network issue (Working through Citrix) and not the formula....

Regardless, I'll try the N on Monday. Thanks Shg, as always.

**Lizabeta** · 05-18-2009, 01:32 PM

Shg,

That formula works great for calculating, but I'm still struggling with nesting it with IF ISERR to suppress the #VALUE on lines that don't contain any information.

(also, can this be changed to unsolved? I thought the issue was taken care of, but appearently my brain was returning #N/A)

**shg** · 05-18-2009, 01:39 PM

=IF(B1=0, "", IF(N(C1), C1/B1, H1/B1))

If that doesn't work, please post an example showing all combinations and desired results.

**Lizabeta** · 05-18-2009, 02:31 PM

The following is notated in the sample workbook as well.

The XRecipe should divide the Planned Total by the Portion Size to get how many times a recipe needs to be made.
If Cindy enters a planned total, that number should be used.
Cindy's Planned Total is brought over from another workbook.
(If there is a number in Column G, column H turns black so the site is discouraged from entering a number in a black box… locking it was too difficult)
If there is no number in Column G, the Site needs to enter a planned total in column H.
I would like to keep all errors suppressed, but the fact that information is coming in from other workbooks seems to be throwing things for a loop. It isn't reading the cell as "0" or "", because there is a big ole " Y:Drive/Menu Production Records/ Cindy's Workbook/Recipe Planning Worksheet path for it to follow