Sumproduct with Autofilter
I have seen several posts that provide recommendations for calculating
Sumproduct when using an autofilter. All of the recommendations require use of the Offset function to achieve the result. While I have tried several of the suggested calculations and they do work correctly, the issue I have is that using Offset makes the recalculation process much lengthier---and because Offset is a "volatile" function it triggers a recalculation anytime I make a change anywhere in the spreadsheet. Two questions: Here is an example of what I'm trying to do: Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200) Range L5:L1000 contains dollar values (i.e. $12.45) I need to calculate the Sumproduct for these two columns, then divide this result by the total unit volume to achieve a weighted average "rate". Here is the formula I am using in row 4 -- again, it does work correctly: =SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3 Is there any other way to calculate this"weighted average rate" without using the Offset function---or any other 'volatile' function? Perhaps using VB in some creative way? I realize a simple solution would be to could carry out the Volume * Rate calculation for each row & then perform the subtotal calculation at the top, but this would require me to insert 64 calculated columns into a worksheet that already contains 205 columns---not very efficient Any assistance or ideas would be greatly appreciated. |
Sumproduct with Autofilter
If you want speed, calculate the sums in another column, say M, =E5*L5 etc.,
and use =SUBTOTAL(9,M:M6)/E3 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JDaywalt" wrote in message ... I have seen several posts that provide recommendations for calculating Sumproduct when using an autofilter. All of the recommendations require use of the Offset function to achieve the result. While I have tried several of the suggested calculations and they do work correctly, the issue I have is that using Offset makes the recalculation process much lengthier---and because Offset is a "volatile" function it triggers a recalculation anytime I make a change anywhere in the spreadsheet. Two questions: Here is an example of what I'm trying to do: Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200) Range L5:L1000 contains dollar values (i.e. $12.45) I need to calculate the Sumproduct for these two columns, then divide this result by the total unit volume to achieve a weighted average "rate". Here is the formula I am using in row 4 -- again, it does work correctly: =SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3 Is there any other way to calculate this"weighted average rate" without using the Offset function---or any other 'volatile' function? Perhaps using VB in some creative way? I realize a simple solution would be to could carry out the Volume * Rate calculation for each row & then perform the subtotal calculation at the top, but this would require me to insert 64 calculated columns into a worksheet that already contains 205 columns---not very efficient Any assistance or ideas would be greatly appreciated. |
Sumproduct with Autofilter
Typo, should be
=SUBTOTAL(9,M:M)/E3 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JDaywalt" wrote in message ... I have seen several posts that provide recommendations for calculating Sumproduct when using an autofilter. All of the recommendations require use of the Offset function to achieve the result. While I have tried several of the suggested calculations and they do work correctly, the issue I have is that using Offset makes the recalculation process much lengthier---and because Offset is a "volatile" function it triggers a recalculation anytime I make a change anywhere in the spreadsheet. Two questions: Here is an example of what I'm trying to do: Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200) Range L5:L1000 contains dollar values (i.e. $12.45) I need to calculate the Sumproduct for these two columns, then divide this result by the total unit volume to achieve a weighted average "rate". Here is the formula I am using in row 4 -- again, it does work correctly: =SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3 Is there any other way to calculate this"weighted average rate" without using the Offset function---or any other 'volatile' function? Perhaps using VB in some creative way? I realize a simple solution would be to could carry out the Volume * Rate calculation for each row & then perform the subtotal calculation at the top, but this would require me to insert 64 calculated columns into a worksheet that already contains 205 columns---not very efficient Any assistance or ideas would be greatly appreciated. |
Sumproduct with Autofilter
Yes, I do realize this would be an option, but I have 64 extra columns that
would have to be created due to the number of "weighted average" calculations/columns I need. Any other options you can think of (i.e. even using VB) that might be more efficient for me? If there is no other option, then I'll just have to figure out which is the lesser of two evils!! Thanks for your help "JDaywalt" wrote: I have seen several posts that provide recommendations for calculating Sumproduct when using an autofilter. All of the recommendations require use of the Offset function to achieve the result. While I have tried several of the suggested calculations and they do work correctly, the issue I have is that using Offset makes the recalculation process much lengthier---and because Offset is a "volatile" function it triggers a recalculation anytime I make a change anywhere in the spreadsheet. Two questions: Here is an example of what I'm trying to do: Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200) Range L5:L1000 contains dollar values (i.e. $12.45) I need to calculate the Sumproduct for these two columns, then divide this result by the total unit volume to achieve a weighted average "rate". Here is the formula I am using in row 4 -- again, it does work correctly: =SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3 Is there any other way to calculate this"weighted average rate" without using the Offset function---or any other 'volatile' function? Perhaps using VB in some creative way? I realize a simple solution would be to could carry out the Volume * Rate calculation for each row & then perform the subtotal calculation at the top, but this would require me to insert 64 calculated columns into a worksheet that already contains 205 columns---not very efficient Any assistance or ideas would be greatly appreciated. |
Sumproduct with Autofilter
Well let's start by stating that an array formula, and SP is an array
formula, it just isn't array entered, is the worst choice in your circumstances. You have too much data, too many formulae to ever make it efficient. The helper columns, whilst many, would be more efficient. VBA is probably the best solution, but if you want some help on that you need to map out the data clearly and determine what will trigger the VBA. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "JDaywalt" wrote in message ... Yes, I do realize this would be an option, but I have 64 extra columns that would have to be created due to the number of "weighted average" calculations/columns I need. Any other options you can think of (i.e. even using VB) that might be more efficient for me? If there is no other option, then I'll just have to figure out which is the lesser of two evils!! Thanks for your help "JDaywalt" wrote: I have seen several posts that provide recommendations for calculating Sumproduct when using an autofilter. All of the recommendations require use of the Offset function to achieve the result. While I have tried several of the suggested calculations and they do work correctly, the issue I have is that using Offset makes the recalculation process much lengthier---and because Offset is a "volatile" function it triggers a recalculation anytime I make a change anywhere in the spreadsheet. Two questions: Here is an example of what I'm trying to do: Range E5:E1000 contains unit volume in whole numbers (i.e. 1,200) Range L5:L1000 contains dollar values (i.e. $12.45) I need to calculate the Sumproduct for these two columns, then divide this result by the total unit volume to achieve a weighted average "rate". Here is the formula I am using in row 4 -- again, it does work correctly: =SUMPRODUCT(SUBTOTAL(3,OFFSET(E4,ROW(E5:E65536)-ROW(E4),1)),E5:E65536,L5:L65536)/E3 Is there any other way to calculate this"weighted average rate" without using the Offset function---or any other 'volatile' function? Perhaps using VB in some creative way? I realize a simple solution would be to could carry out the Volume * Rate calculation for each row & then perform the subtotal calculation at the top, but this would require me to insert 64 calculated columns into a worksheet that already contains 205 columns---not very efficient Any assistance or ideas would be greatly appreciated. |
All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com