Excel Sumproduct

[mikel]

Hi,

I have a worksheet called holiday planner,
i use sumproduct for plotting the dates.
however i need to have another column for whereabouts.
also i use a custom if function to determine the whereabout since sumproduct
returns number.
my formula goes like this
SN = array for name
SW= array for whereabouts
SF=array for FROM
ST=array for T
=((SUMPRODUCT(($G11=sn)*(sw="CRA")*(sf<=H$7)*(st>=H$7)))+0.1)*((SUMPRODUCT(($G11=sn)*(sw="MNL")*(sf<=H$7)*(st>=H$7)))+0.2)*((SUMPRODUCT(($G11=sn)*(sw="PSL")*(sf<=H$7)*(st>=H$7)))+0.3)*((SUMPRODUCT(($G11=sn)*(sw="RTM")*(sf<=H$7)*(st>=H$7)))+0.4)*((SUMPRODUCT(($G11=sn)*(sw="VLA")*(sf<=H$7)*(st>=H$7)))+.5)*((SUMPRODUCT(($G11=sn)*(sw="WAL")*(sf<=H$7)*(st>=H$7)))+.6)*((SUMPRODUCT(($G11=sn)*(sw="HAM")*(sf<=H$7)*(st>=H$7)))+.7)*((SUMPRODUCT(($G11=sn)*(sw="BUD")*(sf<=H$7)*(st>=H$7)))+.8)*((SUMPRODUCT(($G11=sn)*(sw="TOI")*(sf<=H$7)*(st>=H$7)))+.9)*((SUMPRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st>=H$7)))+1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(st>=H$7)))+2)*((SUMPRODUCT(($G11=sn)*(sw="NIT")*(sf<=H$7)*(st>=H$7)))+3)*((SUMPRODUCT(($G11=sn)*(sw="PH")*(sf<=H$7)*(st>=H$7)))+4)*((SUMPRODUCT(($G11=sn)*(sw="TC")*(sf<=H$7)*(st>=H$7)))+5)*((SUMPRODUCT(($G11=sn)*(sw="TE")*(sf<=H$7)*(st>=H$7)))+6)*((SUMPRODUCT(($G11=sn)*(sw="WOO")*(sf<=H$7)*(st>=H$7)))+7)*((SUMPRODUCT(($G11=sn)*(sw="WTF")*(sf<=H$7)*(st>=H$7)))+8)

this works okey for me, however, as i progress in making my holiday planner,
since the rows grows larger, it gives me an error FORMULA TOO LONG.

Can anyone help me?

Thanks in advance

 

[Stefi]

Use helper columns for parts of the formula and refer tothe helper cells in
the final formula! It will be more transparent.

Regards,
Stefi


„mikel†ezt írta:

 

[mikel]

Hi Stefi,

Thanks for you interest in helping, but please if you could be more
specific, im only new to this excel stuff, still learning.
what do you mean by helper columns(CELLS)
could you show me an example.

Thanks

 

[Bob Phillips]

Mikel,

If you explained the business logic behind this, I am sure we could do
better.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

news:[email protected]...

 

[mikel]

Hi Bob,

Thanks for the interest in helping,

most probably, the logic behind the simple holiday planner is known right?
wherein we have column a, b, c as source for the sumproduct function.
a range = SNAMES, b range = SFROM, c range = STO.

but my problem is i want to have another column D for whereabouts like what
is the reason for the holiday, is it Vacation leave, illness etc.

so i want to plot the dates based on NAME and whereabout...

the output should somehow be like this but i want it to automatically plot
the dates in the calendar
INPUT DATA:
A B C D
Snames Sfrom Sto Swhere
Mikel Jan 1 Jan 4 Illness

OUTPUT:

January
1 2 3 4
Mikel ILL ILL ILL ILL
etc.

since sumproduct displays output as number, i have a custom if function
wherein if the output of sumprod is equal to lets say 4 it will display ILL
etc.,
that is the reason why multiply my formula to different number to have
different output based on whereabouts.

Hope you get mine.

and thanks in advance for your help

 

[Bob Phillips]

Hi Bob,

Thanks for the interest in helping,

most probably, the logic behind the simple holiday planner is known right?

Not here!

wherein we have column a, b, c as source for the sumproduct function.
a range = SNAMES, b range = SFROM, c range = STO.

but my problem is i want to have another column D for whereabouts like
what
is the reason for the holiday, is it Vacation leave, illness etc.

so i want to plot the dates based on NAME and whereabout...

the output should somehow be like this but i want it to automatically plot
the dates in the calendar
INPUT DATA:
A B C D
Snames Sfrom Sto Swhere
Mikel Jan 1 Jan 4 Illness

OUTPUT:

January
1 2 3 4
Mikel ILL ILL ILL ILL
etc.

since sumproduct displays output as number, i have a custom if function
wherein if the output of sumprod is equal to lets say 4 it will display
ILL
etc.,
that is the reason why multiply my formula to different number to have
different output based on whereabouts.

Hope you get mine.

and thanks in advance for your help

If I understand, and that is a big if, you want to extract from a table the
number of days of illness and then plot this in another table?

So what happens they are Ill between 1/4 Jan, and on vacation between 10/15.

The way i do this sort of thing is to have a gantt type table, name in
column A, start date in B, end date in C, reasoon in D. In E on in row 1 I
have dates. Then I check in E2 etc like so

=IF(AND($B2<=E$1,$C2>=E$1),VLOOKUP($D2,{"Illness","I";"Holiday","H"},2,FALSE),"")

and probably use conditional formatting to colour the cells for visual
effect.

 

[mikel]

i don't think it would be possible because i have a large list of names, and
i think 20 reason to be on holiday.

could you help me translate this to vba so that i can use this on my formula

my simple equation is :

=SUMPRODUCT(($G11=sn)*(sw="CRA")*(sf<=H$7)*(st>=H$7))

$G11 and H$7 are variables
SN,SW,SF,ST are ranges

a vba function will be great.

thanks

 

[Bob Phillips]

20 reasons is no big deal. Put them in a lookup table, one piece of code.

Why do you want VBA, it won't be efficient.

I still do not understand why you original formula is multiplying all of
those SUMPRODUCTS together, understanding that is the key to giving you a
decent solution. Were you trying to weight each absence type?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)