Can someone help with the below? Thanks. I am familiar with pivot tables and like to use it in Excel to do my computations. The problem is whenever I run it now, the Excel says that it has run into problems and I have to close it. Does anyone know what's the problem here? Also, I would like to know is there any way I can do the below in Excel as now I can't use pivot table to do these now: I download my time and sales into Excel. What I need to do is : 1) Sum all the shares for a particular price (irregardless of time) 2) Currently I am using the 'subtotal function' to sum all shares for a particular price that occurs within around the same price. The problem is I also want to sum and do the average number of shares for each 50 subtotal price levels. What Excel functions can I use to do the below? I will provide an explanation below : 10am $40 = 1000 shares 10.01am $40 = 2500 shares Subtotal $40 = 3500 shares 10.01am $40.01 = 500 shares 10.01 am $40.01= 800 shares 10.02 am $40.01 = 700 shares Subtotal $40.01 = 2000 shares 10.03am $40.03 = 2500 shares 10.04am $40.03= 500 shares Subtotal $40.03 = 3000 shares Thus, for example if I'm interested in the sum and average for 3 price levels, the calculation will be : Sum = 3500 + 2500 + 3000 = 8500 shares Average= 8500/3= 2833 shares Appreciate some help here. Thanks
If you put the subtotals into a separate column you can then put a total at the bottom of that column. It does not matter if there are blank cells in the range. Excel will ignore them. As for you problem with Excel crashing out, it is difficult to say what is happening, without more information. But what you could do is remove the pivot tables and then recreate them. That might fix it. Hope this helps Rob
Thanks for sharing. However, the subtotal is not in a separate column as this is the way Excel subtotal function works. For pivot tables, the Excel just crash before the pivot table can be generated. I've run pivot tables many times in other Excel version and I suspect that most likely this Excel version which I have must have some bugs in it. Anway managed to find a solution to this although a much more tedious one ie breaking up the cell . Thanks once again