| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I need speed calculate when I filter rows.
hi
Iam using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1)),--(N11:N10000="160m"))formula in 50 cells and subtotal function in 10 cells and I am working in a bout 10000R*30C cells.When I use auto filter I must wait a few time to calculate and ready.I need speed calculate. sorry for language problems. thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need speed calculate when I filter rows.
If you click on Tools | Options | Calculation Tab and set calculation
to Manual rather than Automatic, this will greatly speed up Excel's filtering. You can force a recalculation while in this mode by pressing F9. Remember to set it back to Automatic when you are finished. Hope this helps. Pete On Jun 26, 1:05*pm, nader wrote: hi Iam using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1*)),--(N11:N10000="160m"))formula in 50 cells and subtotal function in 10 cells and I am working in a bout 10000R*30C cells.When I use auto filter I must wait a few time to calculate and ready.I need speed calculate. sorry for language problems. thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need speed calculate when I filter rows.
thanks ,but I want both of them.
"Pete_UK" wrote: If you click on Tools | Options | Calculation Tab and set calculation to Manual rather than Automatic, this will greatly speed up Excel's filtering. You can force a recalculation while in this mode by pressing F9. Remember to set it back to Automatic when you are finished. Hope this helps. Pete On Jun 26, 1:05 pm, nader wrote: hi Iam using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1Â*)),--(N11:N10000="160m"))formula in 50 cells and subtotal function in 10 cells and I am working in a bout 10000R*30C cells.When I use auto filter I must wait a few time to calculate and ready.I need speed calculate. sorry for language problems. thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need speed calculate when I filter rows.
Not sure what you mean. You can EITHER have calculation set to
Automatic OR to Manual - when in Manual you can force a recalculation when you want to by pressing F9. Pete On Jun 26, 1:28*pm, nader wrote: thanks ,but I want both of them. "Pete_UK" wrote: If you click on Tools | Options | Calculation Tab and set calculation to Manual rather than Automatic, this will greatly speed up Excel's filtering. You can force a recalculation while in this mode by pressing F9. Remember to set it back to Automatic when you are finished. Hope this helps. Pete On Jun 26, 1:05 pm, nader wrote: hi Iam using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1**)),--(N11:N10000="160m"))formula in 50 cells and subtotal function in 10 cells and I am working in a bout 10000R*30C cells.When I use auto filter I must wait a few time to calculate and ready.I need speed calculate. sorry for language problems. thanks- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need speed calculate when I filter rows.
sorry, I want filtering and calculating synchronous.is this possible fast.
"Pete_UK" wrote: Not sure what you mean. You can EITHER have calculation set to Automatic OR to Manual - when in Manual you can force a recalculation when you want to by pressing F9. Pete On Jun 26, 1:28 pm, nader wrote: thanks ,but I want both of them. "Pete_UK" wrote: If you click on Tools | Options | Calculation Tab and set calculation to Manual rather than Automatic, this will greatly speed up Excel's filtering. You can force a recalculation while in this mode by pressing F9. Remember to set it back to Automatic when you are finished. Hope this helps. Pete On Jun 26, 1:05 pm, nader wrote: hi Iam using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1Â*Â*)),--(N11:N10000="160m"))formula in 50 cells and subtotal function in 10 cells and I am working in a bout 10000R*30C cells.When I use auto filter I must wait a few time to calculate and ready.I need speed calculate. sorry for language problems. thanks- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I need speed calculate when I filter rows.
OFFSET is a volatile function, so if there is a change in the
worksheet (like choosing a filter) then Excel will recalculate these formulae (and dependents) and this is obviously slowing you down. Perhaps you could look at other ways of achieving the results by amending the formulae that you have. Hope this helps. Pete On Jun 26, 2:19*pm, nader wrote: sorry, I want filtering and calculating synchronous.is this possible fast.. "Pete_UK" wrote: Not sure what you mean. You can EITHER have calculation set to Automatic OR to Manual - when in Manual you can force a recalculation when you want to by pressing F9. Pete On Jun 26, 1:28 pm, nader wrote: thanks ,but I want both of them. "Pete_UK" wrote: If you click on Tools | Options | Calculation Tab and set calculation to Manual rather than Automatic, this will greatly speed up Excel's filtering. You can force a recalculation while in this mode by pressing F9. Remember to set it back to Automatic when you are finished. Hope this helps. Pete On Jun 26, 1:05 pm, nader wrote: hi Iam using:=SUMPRODUCT(SUBTOTAL(3,OFFSET(N11:N10000,ROW (N11:N10000)-ROW(N11),0,1***)),--(N11:N10000="160m"))formula in 50 cells and subtotal function in 10 cells and I am working in a bout 10000R*30C cells.When I use auto filter I must wait a few time to calculate and ready.I need speed calculate. sorry for language problems. thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Speed of Excel When Inserting & Deleting Rows | Excel Discussion (Misc queries) | |||
| HOW TO CALCULATE SPEED IF A CAR TRAVELS 600KM IN 4 HRS CARRYING 6. | Excel Worksheet Functions | |||
| Calculate average speed | Excel Discussion (Misc queries) | |||
| Calculate average speed | Excel Worksheet Functions | |||
| How to calculate speed given distance and mm:ss time format? | Excel Discussion (Misc queries) | |||
Linear Mode
