| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Sumproduct
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 TO =((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(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($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)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=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=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
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: 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 TO =((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(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($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)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=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=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
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 "Stefi" wrote: 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: 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 TO =((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(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($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)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=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=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
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) "mikel" wrote in message ... 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 TO =((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(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($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)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=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=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
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" wrote: 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) "mikel" wrote in message ... 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 TO =((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(($G 11=sn)*(sw="RTM")*(sf<=H$7)*(st=H$7)))+0.4)*((SUM PRODUCT(($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)*(s w="BUD")*(sf<=H$7)*(st=H$7)))+.8)*((SUMPRODUCT(($ G11=sn)*(sw="TOI")*(sf<=H$7)*(st=H$7)))+.9)*((SUM PRODUCT(($G11=sn)*(sw="HOL")*(sf<=H$7)*(st=H$7))) +1)*((SUMPRODUCT(($G11=sn)*(sw="ILL")*(sf<=H$7)*(s t=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=s n)*(sw="TC")*(sf<=H$7)*(st=H$7)))+5)*((SUMPRODUCT (($G11=sn)*(sw="TE")*(sf<=H$7)*(st=H$7)))+6)*((SU MPRODUCT(($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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
"mikel" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
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" wrote: "mikel" wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Sumproduct
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) "mikel" wrote in message ... 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" wrote: "mikel" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 Multi Worksheet Sumproduct? | Excel Worksheet Functions | |||
| Multi Worksheet Sumproduct Excel 2003 | Excel Discussion (Misc queries) | |||
| Further Excel help for =SUMPRODUCT((C$1:E$8=G1)*B$1:B$8) | New Users to Excel | |||
| Excel VBA Sumproduct | Excel Worksheet Functions | |||
| Excel : Nesting of functions such as sumproduct and sumif | Excel Worksheet Functions | |||
Linear Mode
