"If" formula results zero

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

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".

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.

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)"?

jfpt4e said:

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)"?

Click to expand...

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:



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

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

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

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