Year to Date - YTD - calculation

[ac3100]

I want to create a Year to Date formula that will add values for the Year to Date months.

If any of the Year to Date months have a value of 0, then I want the result to be 0 because the information is incomplete.

The formula needs to understand which months it should check to see if each month has a valid value. If so, it needs to add those months together.



Jan - 100 Feb - 250 Mar - 200 Apr - 300 May - 0 Jun - 0 Jul - 0 Aug - 0 Sep - 0 Oct - 0 Nov - 0 Dec - 0

In the example above, lets say we are in the month of May. The months from Jan - Apr have values greater than 0 and qualify to be added up. The result is 850



Jan - 100 Feb - 250 Mar - 0 Apr - 300 May - 0 Jun - 0 Jul - 0 Aug - 0 Sep - 0 Oct - 0 Nov - 0 Dec - 0

Same scenario as above except March has a value of 0.

The result should be 0 because March information is missing

Thanks.

 

[Peter_SSs]

See if this helps.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2		100	250	0	0	0	0	0	0	0	0	0	0
3
4	Current Month - 1	2
5	YTD Sum	350
YTD

 

[dcardno]

assuming that your January data is in column B, try

=IF(MIN(OFFSET(B7,0,0,1,5)) > 0,SUM(OFFSET(B5,0,0,1,5)),"n/a")

OFFSET returns a range offset (pretty clever, eh?) from a given reference - in this case the reference cell is B7 (the first argument to the function), and the range is offset by 0 rows and 0 columns (the next two arguemnts) - so it starts in B7. The range is one row high (the fourth argument) and for this example, five columns wide (the last argument). The first time it's used, it is an argument to the MIN function - so it returns the lowest value in the range - if that value is zero, the IF test forks to the "false" branch, and the overall function returns "n/a" (you can substitute a nummeric "0" if that's better for your purposes. The second time the OFFSET function is used it is wrapped into the sum function, which returns the total of the range.

For production use, I would subsitute a cell reference for the "5" in the final argument to both OFFSET functions - setting the value in that cell to 1 to 12 allows the range returned by the OFFSET function to vary from January to December.

 

[Peter_SSs]

It may not be a problem here, but just remember that OFFSET is a volatile function so can impact on your sheet performance if used extensively. I generally try to avoid it if there is a suitable alternative available and that's why I used INDEX here instead of OFFSET.

 

[Haseeb Avarakkan]

Also try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Jan</td><td style=";">Feb</td><td style=";">Mar</td><td style=";">Apr</td><td style=";">May</td><td style=";">Jun</td><td style=";">Jul</td><td style=";">Aug</td><td style=";">Sep</td><td style=";">Oct</td><td style=";">Nov</td><td style=";">Dec</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">100</td><td style="text-align: right;;">250</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Current Month -1</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">350</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet11</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=MONTH(<font color="Blue">TODAY(<font color="Red"></font>)</font>)-1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">(<font color="Green">MONTH(<font color="Purple">1&B1:M1</font>)-1</font>)<=B4</font>),B2:M2</font>)</td></tr></tbody></table></td></tr></table><br />
If your headings are real dates & formatted as "mmm" use SUMIF.

 

[Peter_SSs]

Also try,

That doesn't appear to cater for this condition requested.

If any of the Year to Date months have a value of 0, then I want the result to be 0 because the information is incomplete.


Jan - 100 Feb - 250 Mar - 0 Apr - 300 May - 0 Jun - 0 Jul - 0 Aug - 0 Sep - 0 Oct - 0 Nov - 0 Dec - 0

Same scenario as above except March has a value of 0.

The result should be 0 because March information is missing

 

[ac3100]

Thanks Peter! That solved the problem.

I appreciate everyone's efforts!

Andy