Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
How do identify a blank cell in a formula | Excel Discussion (Misc queries) | |||
how do i create a blank cell in excel using a formula | Excel Worksheet Functions | |||
VLookup resulting in a blank cell... | Excel Worksheet Functions |