"If" formula results zero

[jfpt4e]

I have a vlookup formula in a cell that is pointing to a cell with no values in it yet, so the result is zero. It is possible when there is a result, it could actually be zero.

I have another formula in a cell that adds the previous years amount with the result in the vlookup cell.

My question is if the vlookup cell is pointing to a cell with no values in it yet how can I not show any results (leave blank) in the cell that adds the previous years amount to the vlookup cell?

The problem is I can't just say If(b2>0,a1+b2,"") because the result could actually be 0.

 

[lenze]

Hi and welcome to the board!!
Not really sure what you are asking, but perhaps this is what you want??

=IF(A1,VLOOKUP(A1,YourRange,2,0),"")lenze

 

[jfpt4e]

Hi lenze, thanks for the welcome.

I will try and explain what I mean:

Months 2009: July, August, Sept
Actual MTH: 10,000, 0, 0
Budget MTH: 9,000, 0, 0
Variance MTH: 1,000, 0, 0

Actual YTD: 10,000, 10,000, 10,000
Budget YTD: 9,000, 9,000, 9,000
Variance YTD: 1,000, 1,000, 1,000


I do not want to show the YTD amounts until there is actual results from the VLOOKUP formulas in the MTH's. So in Aug and Sep and so on there are no results yet because the month is not over (the VLOOKUP formulas are in each MTH columns).

In YTD, I want it to be "blank" until there is actual data in the MTH's. Keep in mind there could be a MTH that the actual result returns $0.00. So , in YTD I really could not use "IF mth > $0, add ytd, if not blank".

 

[Expiry]

you could put =today() in Z1 and do an =IF(Z1>yourmonth,0,yourvlookup)

Or something similar. You only want to perform the vlookup when you get to a particular date. Change the above to suit whatever your trigger is going to be.

You could even just put a 1 above each month, as and when you want it to be included and have the IF to only do a vlookup on any month that has a 1 above it.

 

[jfpt4e]

Thanks Expiry.

It appears I could use the =today solution. I have 200 different department spreadsheets to do this for so I want to make it as automated as possible.

When I use =today it shows todays date as 8/13/09. How can I put the date in the if statement "IF Z1<8/15/09,"",myvlookup)"?

 

[yytsunamiyy]

Thanks Expiry.

It appears I could use the =today solution. I have 200 different department spreadsheets to do this for so I want to make it as automated as possible.

When I use =today it shows todays date as 8/13/09. How can I put the date in the if statement "IF Z1<8/15/09,"",myvlookup)"?

do it the other way around - set up your column headers as true dates and custom-format them as "MMMM", then use =IF(Month(A2)<MONTH(TODAY()),YOURLOOOKUP,"")< p>
example:

Excel Workbook
	A	B	C	D	E
1	Year	2009	Cellformat: "YYYY"
2	Month	Januar	Februar	Mrz	Cellformat:"MMMM"
3	today	13.8.2009
4		WAHR
5
6	Year	1.1.2009	Cellformat: General Date
7	Month	1.1.2009	1.2.2009	1.3.2009	Cellformat: General Date
8	today	13.8.2009
9		WAHR
Tabelle1

Cell Formulas
Range	Formula
B3	=TODAY()
B4	=IF(MONTH(B2)

[XR][XD]B8[/XD][XD]=TODAY()[/XD][/XR][XR][XD]B9[/XD][XD]=IF(MONTH(B7)[/XD][/XR]

EDIT:

for some reason the formula is not displaying correctly, here it goes again:<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Year</TD><TD style="TEXT-ALIGN: right">2009</TD><TD>Cellformat: "YYYY"</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Month</TD><TD style="TEXT-ALIGN: right">Januar</TD><TD style="TEXT-ALIGN: right">Februar</TD><TD style="TEXT-ALIGN: right">März</TD><TD>Cellformat:"MMMM"</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>today</TD><TD style="TEXT-ALIGN: right">13.8.2009</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD style="TEXT-ALIGN: right">WAHR</TD><TD></TD><TD></TD><TD><TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Formeln der Tabelle</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>B3</TD><TD>=TODAY()</TD></TR><TR><TD>B4</TD><TD>=IF(MONTH(B2)<MONTH(TODAY()),TRUE,FALSE)

Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4

 

[jfpt4e]

Thanks yytsunamiyy.

It appears the formula is still not showing, it should be this, correct? IF(MONTH(B2)
<MONTH(TODAY()),TRUE,FALSE


Now the problem is when I get in year 2010. The month January is less than today so it is showing the "true" result.

EDIT: I can't get the entire formula to show

 

[yytsunamiyy]

well, simply add a year condition:

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"><COL style="WIDTH: 80px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Year</TD><TD style="TEXT-ALIGN: right">2009</TD><TD>Cellformat: "YYYY"</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Month</TD><TD style="TEXT-ALIGN: right">Januar</TD><TD style="TEXT-ALIGN: right">Februar</TD><TD style="TEXT-ALIGN: right">März</TD><TD>Cellformat:"MMMM"</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>today</TD><TD style="TEXT-ALIGN: right">13.8.2009</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: right">TRUE</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Formeln der Tabelle</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Zelle</TD><TD>Formel</TD></TR><TR><TD>B3</TD><TD>=TODAY()</TD></TR><TR><TD>B4</TD><TD>=IF(AND(MONTH(B2)<MONTH(TODAY()),YEAR(B2)<=YEAR(TODAY())),TRUE,FALSE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel Tabellen im Web darstellen >> http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4

 

[jfpt4e]

That worked, thank you (and all) for your help!