SUM Matching Monthly Columns to Give a Variance

I have two sets of columns, the first M1 to M12 giving forecast figures, followed by an overall Total, then a second set giving M1 to M12 actuals (see attached). I need to be able to provide a variance in column AA for each row which is the difference between the Forecast YTD figure and the Actual YTD figure up to the current month. The current month will be defined by the column before the column where the sum of a column = 0, rather than by using the actual date.

So it needs to check the sum of each column in the M1 to M12 actuals until it finds the first column that equals 0 (note it is possible for there to be zero values in cells within a column, i.e. a zero actual) and then SUM the actuals between M1 and the Month where the SUM of the column <>0, (M3 in the attached) and then subtract the equivalent YTD total for the same months in the M1 to M12 forecast to give the difference in column AA on a row by row basis.

So in the example the first column in the M1 to M12 actuals to = 0 is column Q, so for each row it needs to SUM(N2:P2) - SUM(A2:C2) = -1,763

Likewise if the total of column R >0 it would give te YTD variance for the first 4 months and so on.

I've been playing around with OFFSET, MATCH and INDEX but still can't figure it out.

Many thanks

I swapped the order in the last set of parenthesis because the variance needs to be 'actual' minus 'forecast' but either way, if I copy the formula down I get a #Value! error in rows, 5, 15 and 20!

Please Login or Register  to view this content.

Whoops, hadn't spotted those... The position of my columns on my main sheet start in column AB rather than A and the sheets will conntinually have new rows added. Other than adjusting the N:Y and A:L references, would I need to adjust anything else for this to still work? I can't access the file at the moment!

Okay, perfect, many thanks, I would never have figured this one out...

One further thought, Whilst this works well and provides a solution, I notice that if any of the cells are blank, the formula (by the nature of using MMULT) won't work. The data is going to be entered by the end users who have a habit of leaving cells blank rather than entering a zero when there is no value, so this could cause a problem. Are there any alternative solutions or ways to adapt this formula that would overcome this potential problem?

Okay, thanks, that seems to fix the problem...

Many thanks

Okay, so for the life of me I can not see the error. I've transposed the formula to my main sheet but I am getting #Value! as the result. Have I made an error here that I'm just not seeing?

Please Login or Register  to view this content.

Out of interest, what does the *1 part actually do?

Okay, figured it out, $A$1 has to remain as that...

nflsales,

Is there an easy way to adapt your SUMPRODUCT formula to be able to display the variance as a percentage, the logic being:

Please Login or Register  to view this content.

so if $N2:$Y2 = 10 and $A2:$L2 = 20, then the variance would be -10 but as a percentage it would be ABS((10-20)/20) = 50% because you can't have negative variance.

I've been struggling with how to do this and have so far failed to get it to work!

Many thanks