Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
I must have used a subject for an earlier post by someone else and
this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out: http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
Of course
=SUMPRODUCT(--(myRange=1700),--(myRange<1800)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ups.com... I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out: http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
It looks like your dynamic range height is based on the last numeric value
in column A and the range is 20 columns wide. To use that named range you have to wrap it inside an INDEX function and tell SUMPRODUCT which column of the range to use. Like this: =SUMPRODUCT(--(INDEX(MyRange,,1)="this"),--(INDEX(MyRange,,2)="that"),INDEX(MyRange,,3)) In the above I'm referencing the first, second and third columns of the dynamic range. See this for suggestions on improving calculation efficiency: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "Ken" wrote in message ups.com... I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out: http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
You might like to switch the file to manual calculation while you
enter the data - Tools | Options | Calculation tab, click on Manual. This will stop the sheet from recalculating while you put data in. You can then press F9 to recalculate once you are ready, or Tools | Options | Calculation tab, click on Automatic. Hope this helps. Pete On Nov 10, 4:07 pm, Ken wrote: I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out:http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
On Nov 10, 3:52 pm, Pete_UK wrote:
You might like to switch the file to manual calculation while you enter the data - Tools | Options | Calculation tab, click on Manual. This will stop the sheet from recalculating while you put data in. You can then press F9 to recalculate once you are ready, or Tools | Options | Calculation tab, click on Automatic. Hope this helps. Pete On Nov 10, 4:07 pm, Ken wrote: I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out:http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken- Hide quoted text - - Show quoted text - Thanks Pete for the advice...I'll certainly use it! Ken |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
On Nov 10, 1:01 pm, "T. Valko" wrote:
It looks like your dynamic range height is based on the last numeric value in column A and the range is 20 columns wide. To use that named range you have to wrap it inside an INDEX function and tell SUMPRODUCT which column of the range to use. Like this: =SUMPRODUCT(--(INDEX(MyRange,,1)="this"),--(INDEX(MyRange,,2)="that"),INDEX*(MyRange,,3)) In the above I'm referencing the first, second and third columns of the dynamic range. See this for suggestions on improving calculation efficiency: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "Ken" wrote in message ups.com... I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out: http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken- Hide quoted text - - Show quoted text - Biff, your formula worked extremely well....the calculation speeds have been significantly faster....again thanks for your help! Bob thanks for your help also! Ken |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
"Ken" wrote in message
s.com... On Nov 10, 1:01 pm, "T. Valko" wrote: It looks like your dynamic range height is based on the last numeric value in column A and the range is 20 columns wide. To use that named range you have to wrap it inside an INDEX function and tell SUMPRODUCT which column of the range to use. Like this: =SUMPRODUCT(--(INDEX(MyRange,,1)="this"),--(INDEX(MyRange,,2)="that"),INDEX*(MyRange,,3)) In the above I'm referencing the first, second and third columns of the dynamic range. See this for suggestions on improving calculation efficiency: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "Ken" wrote in message ups.com... I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out: http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken- Hide quoted text - - Show quoted text - Biff, your formula worked extremely well....the calculation speeds have been significantly faster....again thanks for your help! Bob thanks for your help also! Ken You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Advice
Glad to hear that - thanks for taking the trouble to feed back.
Pete On Nov 10, 9:51 pm, Ken wrote: On Nov 10, 3:52 pm, Pete_UK wrote: You might like to switch the file to manual calculation while you enter the data - Tools | Options | Calculation tab, click on Manual. This will stop the sheet from recalculating while you put data in. You can then press F9 to recalculate once you are ready, or Tools | Options | Calculation tab, click on Automatic. Hope this helps. Pete On Nov 10, 4:07 pm, Ken wrote: I must have used a subject for an earlier post by someone else and this showed up twice in their reply, so I have changed the subject name..... Hi Group! I'm back again, trying to solve a problem that Don Guillett so correctly pointed out that needs to be addressed, and I thank you for that Don! I have a database that dynamically updates from log entries, which in turn updates my charts, and they are working extremely well. The problem is that I'm using SUMPRODUCT in every cell in my daily database, and that is causing the log entries to become slower to enter because the database recalculates on every entry, and my formula includes far too many cells in that calculation. I have entered a dynamic named range "MyRange" that correctly includes only the rows that are populated, but I haven't been able to include that named range into the calculations for SUMPRODUCT. I have screenshots here to illustrate how everything is laid out:http://www.elodgingatbristol.com/SUMPRODUCT.htm Can that named range be used in the formula to limit how many calculations that Excel has to do? Any advice is greatly appreciated...I've beat my head against the wall for a couple of weeks trying different tips that I've seen in the groups, but can't get it to work. Thanks very much in advance.... Ken- Hide quoted text - - Show quoted text - Thanks Pete for the advice...I'll certainly use it! Ken- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
any advice pls.. | Excel Discussion (Misc queries) | |||
Advice on Creating an Excel Formula or Macro - PLEASE HELP!!!! | Excel Worksheet Functions | |||
need formula advice | Excel Worksheet Functions | |||
Formula Advice Needed | Excel Discussion (Misc queries) | |||
Almost got it !! but need advice | Excel Worksheet Functions |