Optimize a sumproduct function (or find another solution)

[Andrea]

Hello,
this is my excel problem.
I have a table so structured (I add holiday column to explain my question,
but originally there is not in the table)

holiday day num
y 20/12 3
y 21/12 4
n 22/12 5
n 23/12 6
y 24/12 1
y 25/12 2
y 26/12 5
y 27/12 3
n 28/12 4

I have to extract, in another sheet, numbers according the day.
Main problem is that if I have a weekday after one or more consecutive
holidays
(I consider saturday and sunday like holidays), I have to sum the weekday
value
to the previous holiday(s) number(s).

So in the specified case, in another sheet, I have to find a situation like
this:

newdata total
22/12 12 (the sum of 22/12(w) + 21/12(h) + 20/12(h) values)
23/12 6 (only 23/12 value as weekday without previous
holidays)
28/12 15 (the sum of 28/12(w) + 27/12(h) + 26/12(h) +
25/12(h) + 24/12(h))


now, I set this kind of formula (where X and Z after "day", "newdata" and
"num" variables, are row and columns references):
IF(newdatax-(newdatax-1) = 5;
SUMPRODUCT((dayx:dayz=newdatax-4)*(dayx:dayz=newdatax-3)*(dayx:dayz=newdatax-2)*(dayx:dayz=newdatax-1)*(dayx:dayz=newdatax);(numx:numz);
IF(newdatax-(newdatax-1) = 4;
SUMPRODUCT((dayx:dayz=newdatax-3)*(dayx:dayz=newdatax-2)*(dayx:dayz=newdatax-1)*(dayx:dayz=newdatax);(numx:numz);............ )

and so on until arrive to the condition IF(newdata-(newdata-1) =
1;(dayx:dayz=newdatax);(numx:numz)

this kind of formula works, but is too long because in this way I have to
consider all possibility, starting from a maximum of 4 consecutive holidays
dates (that may occur under Christmas time, but it could happen).
My question is: is there a possibility to build up another formula more
optimized and short ?
I hope to explain well my trouble and I wait some suggestion about.
thanks.


Andrea.

 

[smartin]

Hello,
this is my excel problem.
I have a table so structured (I add holiday column to explain my question,
but originally there is not in the table)

holiday day num
y 20/12 3
y 21/12 4
n 22/12 5
n 23/12 6
y 24/12 1
y 25/12 2
y 26/12 5
y 27/12 3
n 28/12 4

I have to extract, in another sheet, numbers according the day.
Main problem is that if I have a weekday after one or more consecutive
holidays
(I consider saturday and sunday like holidays), I have to sum the weekday
value
to the previous holiday(s) number(s).

So in the specified case, in another sheet, I have to find a situation like
this:

newdata total
22/12 12 (the sum of 22/12(w) + 21/12(h) + 20/12(h) values)
23/12 6 (only 23/12 value as weekday without previous
holidays)
28/12 15 (the sum of 28/12(w) + 27/12(h) + 26/12(h) +
25/12(h) + 24/12(h))


now, I set this kind of formula (where X and Z after "day", "newdata" and
"num" variables, are row and columns references):
IF(newdatax-(newdatax-1) = 5;
SUMPRODUCT((dayx:dayz=newdatax-4)*(dayx:dayz=newdatax-3)*(dayx:dayz=newdatax-2)*(dayx:dayz=newdatax-1)*(dayx:dayz=newdatax);(numx:numz);
IF(newdatax-(newdatax-1) = 4;
SUMPRODUCT((dayx:dayz=newdatax-3)*(dayx:dayz=newdatax-2)*(dayx:dayz=newdatax-1)*(dayx:dayz=newdatax);(numx:numz);............ )

and so on until arrive to the condition IF(newdata-(newdata-1) =
1;(dayx:dayz=newdatax);(numx:numz)

this kind of formula works, but is too long because in this way I have to
consider all possibility, starting from a maximum of 4 consecutive holidays
dates (that may occur under Christmas time, but it could happen).
My question is: is there a possibility to build up another formula more
optimized and short ?
I hope to explain well my trouble and I wait some suggestion about.
thanks.

Hi Andrea,

Seems to me it would be far simpler to put this in cell D2:
=IF(A1="n",C2,D1+C2)

This gives a running sum on holidays, and the correct total on each
non-holiday.

Now you said the holiday field is not actually in your data. But you
must have a table somewhere that maps dates to holidays? We could
leverage that table where it sits to get the holiday status. The logic
is the same here, it's just a trickier lookup:

+----- All of this replaces "IF(A1" above ----+
| |
=IF(INDEX($A$1:$A$10,MATCH(B2,$B$1:$B$10,0)-1,0)="n",C2,D1+C2)
^^^^^^^^^^ ^^^^^^^^^^
^^ The location of your holidays table ^^
^^ make sure it's sorted! ^^

At the end of the day I think it would be far simpler to bring the
holiday status into the table you are analyzing, e.g., by using VLOOKUP,
and using the first formula.