Help with formula to make cell blank
 

In T2 I have this formula:

=IF((N2<(U2-80)),U2-(N2+80),0)

In U2 I have this formula:

=IF(A20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)*1 0,"")

Once I enter a number in A2, all formulas work fine and I get the desired
result. However, when A2 is blank, #VALUE is displayed in T2 because of
formula in U2. I need T2 to be blank until a number is entered in A2. I'm
sure it is a fairly easy fix but I can't work it out. Can someone help with
this? Thanks!

Hi

IF(U2="","",MAX(U2-N2-80,0))
or even better
IF(OR(U2="",N2=""),"",MAX(U2-N2-80,0))


Arvi Laanemets


"Inspector" wrote in message
...
In T2 I have this formula:

=IF((N2<(U2-80)),U2-(N2+80),0)

In U2 I have this formula:

=IF(A20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)*1 0,"")

Once I enter a number in A2, all formulas work fine and I get the desired
result. However, when A2 is blank, #VALUE is displayed in T2 because of
formula in U2. I need T2 to be blank until a number is entered in A2.
I'm
sure it is a fairly easy fix but I can't work it out. Can someone help
with
this? Thanks!

How about:

=IF(U2="","",IF(N2<(U2-80),U2-(N2+80),0))

in cell T2?

Tom Hayakawa

"Inspector" wrote:

In T2 I have this formula:

=IF((N2<(U2-80)),U2-(N2+80),0)

In U2 I have this formula:

=IF(A20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)*1 0,"")

Once I enter a number in A2, all formulas work fine and I get the desired
result. However, when A2 is blank, #VALUE is displayed in T2 because of
formula in U2. I need T2 to be blank until a number is entered in A2. I'm
sure it is a fairly easy fix but I can't work it out. Can someone help with
this? Thanks!

Inspector wrote...
In T2 I have this formula:

=IF((N2<(U2-80)),U2-(N2+80),0)

Unnecessary parentheses. Also, no IF needed. Try

=MAX(0,U2-N2-80)

In U2 I have this formula:

=IF(A20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)* 10,"")
...

Looks like you made a typo, and the formula should be

=IF(A20,VLOOKUP(A2,SHEET1!$A$2:$C$288,3,FALSE)*10 ,"")

. . . However, when A2 is blank, #VALUE is displayed in T2 because of
formula in
U2. I need T2 to be blank until a number is entered in A2. . . .

Change T2 to

=IF(A20,MAX(0,U2-T2-80),"")


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=315094

Hi,
Try this.
=IF(U2="","",(IF((N2<(U2-80)),U2-(N2+80),0)))
Hope I got the ()s right.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=315094

thanks! works great!

"Tom Hayakawa" wrote:

How about:

=IF(U2="","",IF(N2<(U2-80),U2-(N2+80),0))

in cell T2?

Tom Hayakawa

"Inspector" wrote:

In T2 I have this formula:

=IF((N2<(U2-80)),U2-(N2+80),0)

In U2 I have this formula:

=IF(A20,VLOOKUP(A2'SHEET1'!$A$2:$C$288,3,FALSE)*1 0,"")

Once I enter a number in A2, all formulas work fine and I get the desired
result. However, when A2 is blank, #VALUE is displayed in T2 because of
formula in U2. I need T2 to be blank until a number is entered in A2. I'm
sure it is a fairly easy fix but I can't work it out. Can someone help with
this? Thanks!