| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: sumproduct variation question
I'm currently extracting data from a larger table to a smaller summary table
(on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$14),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Don, many thanks....unfortunately, from the equations you offered, I'm still
unable to figure out how to apply the formula to just those rows that meet three or more conditions (as I showed in the SUMPRODUCT formula below). It's akin to a triple vlookup, though no qualifiying data falls in the left column. Once I've targeted those rows, then I need to perform AVERAGE, COUNTIF, etc on a certain column... Let me know if you have any ideas and forgive me if I misunderstood your reponse. Marika "Don Guillett" wrote: try these ideas. You should be able to figure it out from there maybe this ARRAY formula to be entered with control+shift+enter instead of just enter =COUNT(IF(($B$1:$B$5=B1)*($A$1:$A$5),1)) =COUNT(IF(A2:A4,1,B2:B4)) =AVERAGE(IF(rngSMALL(rng,4),IF(rng<LARGE(rng,4),r ng))) =AVERAGE(IF((A1:A4="jones")*(C1:C40),C1:C4)) =AVERAGE(IF(K7:K220,K7:K22)) =AVERAGE(IF($H$3:$H$23=C11,$K$3:$K$23) -- Don Guillett SalesAid Software "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
(Untested):
=SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Average :
=AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why
use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Thank you so much Vasant and Toppers - your suggestions *virtually* did the
trick, though I have two small problems (one of which Tom alluded to) 1) When using the AVERAGE(IF) solution Toppers offered: =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1),CNTXTOrders)) it does include all zero values. 2) When using the count solution from Vasant: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$14),--(CNTXTOrders<=10)) it counts all zero values and all blank cells. Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the count formula??? I can't thank you enough - you've saved my day! Marilka :) "Tom Ogilvy" wrote: That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Sorry:
=SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders0),--(CNTXTOrders<10)) should work for your second requirement. -- Vasant "marika1981" wrote in message ... Thank you so much Vasant and Toppers - your suggestions *virtually* did the trick, though I have two small problems (one of which Tom alluded to) 1) When using the AVERAGE(IF) solution Toppers offered: =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) it does include all zero values. 2) When using the count solution from Vasant: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) it counts all zero values and all blank cells. Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the count formula??? I can't thank you enough - you've saved my day! Marilka :) "Tom Ogilvy" wrote: That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
=SUMPRODUCT(--(CNTXTName="Shared
Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<"")) I stand corrected on Toppers formula. I did not see the "IF" in the formula. Anyway if you want to exclude zeros or blanks if a cell in CNTXTOrders would otherwise be included, then: =AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)* (CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)* (CNTXTOrders<""),CNTXTOrders) -- Regards, Tom Ogilvy "marika1981" wrote in message ... Thank you so much Vasant and Toppers - your suggestions *virtually* did the trick, though I have two small problems (one of which Tom alluded to) 1) When using the AVERAGE(IF) solution Toppers offered: =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) it does include all zero values. 2) When using the count solution from Vasant: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) it counts all zero values and all blank cells. Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the count formula??? I can't thank you enough - you've saved my day! Marilka :) "Tom Ogilvy" wrote: That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
OK Tom, let's call it quits as I didn't think about blanks and zeros when I
tested my offering. Must do better! "Tom Ogilvy" wrote: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<"")) I stand corrected on Toppers formula. I did not see the "IF" in the formula. Anyway if you want to exclude zeros or blanks if a cell in CNTXTOrders would otherwise be included, then: =AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)* (CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)* (CNTXTOrders<""),CNTXTOrders) -- Regards, Tom Ogilvy "marika1981" wrote in message ... Thank you so much Vasant and Toppers - your suggestions *virtually* did the trick, though I have two small problems (one of which Tom alluded to) 1) When using the AVERAGE(IF) solution Toppers offered: =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) it does include all zero values. 2) When using the count solution from Vasant: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) it counts all zero values and all blank cells. Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the count formula??? I can't thank you enough - you've saved my day! Marilka :) "Tom Ogilvy" wrote: That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
|
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Thank you all so much for your help.....I *very* much appreciate it.
However, I'm still unable to get the AVERAGE formula to ignore zero values. I've checked the formula I entered four or five times - here it is on the tab i'm currently working on: =AVERAGE(IF((KREMonthRange=F$16)*(KREYearRange=F$1 5)*(KREAltaVista<0)*(KREAltaVista<""),(KREAltaVi sta))) (I do get an answer - but it's the average including zeros) What have I got wrong????? Marika :) "Tom Ogilvy" wrote: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<"")) I stand corrected on Toppers formula. I did not see the "IF" in the formula. Anyway if you want to exclude zeros or blanks if a cell in CNTXTOrders would otherwise be included, then: =AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)* (CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)* (CNTXTOrders<""),CNTXTOrders) -- Regards, Tom Ogilvy "marika1981" wrote in message ... Thank you so much Vasant and Toppers - your suggestions *virtually* did the trick, though I have two small problems (one of which Tom alluded to) 1) When using the AVERAGE(IF) solution Toppers offered: =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) it does include all zero values. 2) When using the count solution from Vasant: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) it counts all zero values and all blank cells. Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the count formula??? I can't thank you enough - you've saved my day! Marilka :) "Tom Ogilvy" wrote: That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Hi again,
It's late here ... but I can't see anything wrong with the formula. I have retested your originals with Tom's changes and they work fine. I then modified the original AVERAGE one to reflect your new one and it also works OK: if I take out the '<0' test then 0s are included (as expected) but removed if the test is included. "marika1981" wrote: Thank you all so much for your help.....I *very* much appreciate it. However, I'm still unable to get the AVERAGE formula to ignore zero values. I've checked the formula I entered four or five times - here it is on the tab i'm currently working on: =AVERAGE(IF((KREMonthRange=F$16)*(KREYearRange=F$1 5)*(KREAltaVista<0)*(KREAltaVista<""),(KREAltaVi sta))) (I do get an answer - but it's the average including zeros) What have I got wrong????? Marika :) "Tom Ogilvy" wrote: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10),--(CNTXTOrders<0),--(CNTXTOrders<"")) I stand corrected on Toppers formula. I did not see the "IF" in the formula. Anyway if you want to exclude zeros or blanks if a cell in CNTXTOrders would otherwise be included, then: =AVERAGE(IF((CNTXTName="Shared Hosting")*(CNTXTProd=$C253)* (CNTXTMonthRange=E415)*(CNTXTYearRange=E$1)*(CNTXT Orders<0)* (CNTXTOrders<""),CNTXTOrders) -- Regards, Tom Ogilvy "marika1981" wrote in message ... Thank you so much Vasant and Toppers - your suggestions *virtually* did the trick, though I have two small problems (one of which Tom alluded to) 1) When using the AVERAGE(IF) solution Toppers offered: =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) it does include all zero values. 2) When using the count solution from Vasant: =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) it counts all zero values and all blank cells. Is there a way to specify 0 in the AVERAGE(IF) formula and 0 & <10 for the count formula??? I can't thank you enough - you've saved my day! Marilka :) "Tom Ogilvy" wrote: That would average all CNTXTOrders plus a bunch of 1's and Zero's. Plus why use both mulitplication and double negatives? -- Regards, Tom Ogilvy "Toppers" wrote in message ... Average : =AVERAGE(IF(--(CNTXTName="Shared Hosting")*--(CNTXTProd=$C253)*--(CNTXTMonthRange=E415)*--(CNTXTYearRange=E$1 ),CNTXTOrders)) "Vasant Nanavati" wrote: (Untested): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders)/SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4)) should give you the average. =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),--(CNTXTOrders<=10)) should give you the number of occurrences less than or equal to 10. -- Vasant "marika1981" wrote in message ... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$1 4),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
URGENT: sumproduct variation question
Wouldn't this be far simpler to solve by treating the source as a
database and using the very powerful techniques developed over the decades for data access and analysis? -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I'm currently extracting data from a larger table to a smaller summary table (on a different tab) using the SUMPRODUCT function in this form (an example): =SUMPRODUCT(--(CNTXTName="Shared Hosting"),--(CNTXTProd=$C253),--(CNTXTMonthRange=E$15),--(CNTXTYearRange=E$14),CNTXTOrders) to add the values of CNTXTOrders in the rows that meet the preceeding given conditions in the formula. QUESTION: I need to find a way to do the same operation but AVERAGE the values (instead of summing them) and COUNT the occurence of values that are equal to or less than 10. Can anyone advise on a way to do this??? Unfortunately this is part of an elaborate spreadsheet, so I'm structurally limited (can't rearrange the data). Any advice would be so much appreciated as I'm already past my deadline. Many, many thanks, Marika :) :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct with variation | Excel Discussion (Misc queries) | |||
| Need urgent help with SUMPRODUCT() | Excel Discussion (Misc queries) | |||
| Sumproduct-Countif variation | Excel Worksheet Functions | |||
| Question Variation: Shading a portion of the space between two lines | Charts and Charting in Excel | |||
| URGENT: Please Advise. SumProduct and Operand Question | Excel Discussion (Misc queries) | |||
Linear Mode
