Excel YTD formula to display YTD value based on the month selected in a validation list?? 1

[knifey]

Hi,

I'm trying to create a formula that calculates the YTD value for April (start of our fiscal year) to the month selected in a validation list.

I have multiple columns per month as shown below:
Row 6 March March March March March March
Row 7 Volume Volume ASP ASP Value Value
Row 8 Budget Actual Budget Actual Budget Actual
13 99 23 13 17 15

April starts in column H and March finishes in column CA.
I also have a validation list with the 12 month names in cell A1.

I've tried the following formulas but they only seem to be showing the value of the currently selected month (instead of April to the selected month):
=SUM(H10:INDEX(H10:CA10,MAX(MATCH($A$1,$H$6:$CA$6,0),MATCH("Volume",$H$7:$CA$7,0),MATCH("Budget",$H$8:$CA$8,0))))
=SUM(H10:INDEX(H10:CA10,AND(MATCH($A$1,$H$6:$CA$6,0),MATCH("Volume",$H$7:$CA$7,0),MATCH("Budget",$H$8:$CA$8,0))))
=SUM(H10:INDEX(H10:CA10,MATCH($A$1&"Volume"&"Budget",$H$6:$CA$6&$H$7:$CA$7&$H$8:$CA$8,0)))

Can anyone advise where I'm going wrong?
Any help would be much appreciated.
Thanks,
K

 

[SkipVought]

hi,

Just a general comment first:

What the heck is your formula referring to?

There is nothing in your explanation that you even give a hint of what is in column H row 10 to ?????

Where's the Validation List Cell?

Its a MESS to try to understand.

Why aren't you using Named Ranges or a Structured Table. Presumably your are using Excel 2007+, so Structured Tables ought to be a common occurrence in your Excel vocablulary, as it is a HUGE step forward in Excel feature technology. Help yourself out.

OKAY, so how about a SAMPLE of data for the columns/rows in question. I seriously doubt if row 6 contains "March" in columns A:F of B:G. But on the other hand, I have been know to be wrong, and I don't wish to make an [highlight #FCE94F]ass[/highlight] of [highlight #FCE94F]u[/highlight] and [highlight #FCE94F]me[/highlight]. ;-)



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,
I have attached a sample of my workbook. My formulas attempts so far are in column CI and my validation list is in A1.
Row 10 onwards contains the various figures that I need ytd values for based on cell A1.

Here is a link to a working version of what I am trying to do (found on another post). But this calculates ytd based on selected month only (1 criteria). I need selected month/Volume/Budget, selected month/Volume/Actual, selected month/ASP/Budget, selected month/ASP/Actual, etc., etc.
So what I think I need is an offset match formula that can handle multiple match criteria.
Cells T39:U46 of this workbook contains my unsuccessful attempts at making this work using AND.
Book2:

https://drive.google.com/file/d/0Bx5r9tIItS8VaGNDb1pmSlpiUms/edit?usp=sharing

Any help would be much appreciated.

Thanks,
K

 

[knifey]

Bump, anyone?

 

[SkipVought]

The problem is in your OFFSET, using the CI formula in row 10...
[pre]
OFFSET
(
$H10,
MATCH($A$1,$H$6:$CA$6,0)-1,
MATCH("Volume",$H$7:$CA$7,0),
MATCH("Budget",$H$8:$CA$8,0)
)
[/pre]
Offset has 5 arguments:
arg1 - the ANCHOR CELL: yours is H10, the beginning of the fiscal year for this row--looks fine!
arg2 - the ROW OFFSET from the anchor cell: Hmmmmmmm? I'd guess that this ought to be ZERO (only this row)!!!
arg3 - the COLUMN OFFSET from the anchor cell: Hmmmmmmm? I'd guess that since you want YTD, ie from April to the Selected Month, this should ALSO BE ZERO!!!
arg4 - the ROW RANGE to be returned
arg5 - the COL RANGE to be returned: this range should encompass the columns in the referenced row from H10 to the column containing the selected month.

However, this range contains values from OTHER than Volume/Budget!!!

This is why trying to use a REPORT (your pretty sheet is a NON NORMALIZED REPORT) which is virtually useless as a source for this kind of analysis. Your data ought to be in a proper table from which such a report can be fairly simply generated, including the YTD data you desire.
[pre]
INN|MIS SKU -CURRENT YEAR|str val|Form|Strength|Pack Size|Prod type|Act Yr-Mo|Act Type|Act Value
Aceclofenac|Aceclofenac_tab_#_100mg_10|100|tab|100mg|10|Gx|2013-04-01|Volume|1
Aceclofenac|Aceclofenac_tab_#_100mg_10|100|tab|100mg|10|Gx|2013-04-01|ASP|1
Aceclofenac|Aceclofenac_tab_#_100mg_10|100|tab|100mg|10|Gx|2013-04-01|Value|1
...
[/pre]

I NORMALIZED your data faq68-5287 as above and made the table a Structured Table named tDRG

My formula for Volume Budget:
[tt]
=SUMPRODUCT((tDRG[MIS SKU -CURRENT YEAR]=B19)*(tDRG[Act Yr-Mo]>=DATE(2013,4,1))*(tDRG[Act Yr-Mo]<=DATE(2013,11,1))*(tDRG[Act Typ]="Volume")*(tDRG[Act BA]="Budget")*(tDRG[Act Value]))
[/tt]
The problem is specifying the thru date. I hard-coded novenber as you can see. I use REAL DATES as string are virtually useless in a table. But if you would use the [highlight #FCE94F]Month Offset[/highlight] from april, nov is +7 months, so the date would be

DATE(Year(StartFiscalYr),Month(StartFiscalYr)+[highlight #FCE94F]MonthOffset[/highlight],1)



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Non-Normalized data aside, you sure shot yourself in the foot in so many ways.

It is possible to SUM the data in each row, based on parameters such as the Start FY and Selected Month, BUT...

1. You need REAL DATES
2. You need HEADING DATA IN EACH CELL IN THE HEADINGS
3. You need ZERO values where your lookup data returns an error in your ASP columns (format them as accounting with the - as ZERO)

So here's how:

Your interactive YTD area (Yellow extended) CI6:
[pre]
YT SelectedMO
Volume Volume ASP ASP Value Value
Budget Actual Budget Actual Budget Actual

[/pre]
Formula CI10:
[tt]
CI10: =SUMPRODUCT(($H$6:$CA$6>=StartFiscalYR)*($H$6:$CA$6<DATE(YEAR(SelectedMO), MONTH(SelecteedMO)+1,1))*($H$7:$CA$7=CI$7)*($H$8:$CA$8=CI$8)*($H10:$CA10))
[/tt]
Notice I useed Named Range for StartFiscalYR and SelectedMO [highlight #FCE94F]WHICH ARE BOTH REAL DATES[/highlight]

HOWEVER, you will not be able to use this formula unless your modify your sheet according to my 3 issues ablve!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

here are the results down to row 19 on the YT SelectedMO (after I replaces "-" with 0 in your ASP formulas:

[pre]
YT SelectedMO
Volume Volume ASP ASP Value Value
Budget Actual Budget Actual Budget Actual
7407 1 125002 1 126637 1
8 8 8 8 8 8
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 0 0 0 0 0
1096 0 4 0 574 0
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,

I've tried the solution from your last post but I can't get it to work. It's coming up with a #NAME? error in MONTH(SelecteedMO). Strange, but the YEAR(SelecteedMO) part works fine.
Please can you take a quick look if you have time. I know I'm almost there!

https://drive.google.com/file/d/0Bx5r9tIItS8VdDhDcjU3OF9ZS0U/edit?usp=sharing

Thanks for your help,
K

 

[SkipVought]

SelectedMO - Sorrrry for the heavy fingeeer

=SUMPRODUCT(($H$6:$CA$6>=StartFiscalYR)*($H$6:$CA$6<DATE(YEAR(SelectedMO), MONTH(SelectedMO)+1,1))*($H$7:$CA$7=CI$7)*($H$8:$CA$8=CI$8)*($H10:$CA10))

returns 5

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

BTW, I would only hard code a date in the first cell and all others would be a formula!

Then just change the start date and all others calculate accordingly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[knifey]

Hi Skip,
Thanks for this amazing piece of code, it works great! I can't believe I didn't see the extra eee.

Cheers,
K