Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a simple subtotal Formula
Hi Let's say i have a column like this: A __ B 1 1 1 1__formula says "4" empty empty empty 1 1 1 1 1__formula says "5" Is there a formula I can place in B to subtotal what is in A? All I want to do is get the totals for each little group on the way down. I am trying to get around the Subtotals ... menu command in the Data Menu Thanks tx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=505199 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a simple subtotal Formula
Is =SUMIF(A:A,1) any good?
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "tx12345" wrote in message ... Hi Let's say i have a column like this: A __ B 1 1 1 1__formula says "4" empty empty empty 1 1 1 1 1__formula says "5" Is there a formula I can place in B to subtotal what is in A? All I want to do is get the totals for each little group on the way down. I am trying to get around the Subtotals ... menu command in the Data Menu Thanks tx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=505199 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a simple subtotal Formula
Hi Bob, Thanks for the idea, but =sumif(A:A,1) totals every instance of 1 for the entire column and returns the result. Here is the hack I came up with: in W11 and on down as needed: =IF(AND(S10=0,S11=1,S12=1),ROW(),IF(AND(S10=1,S11= 1,S12=0),ROW()+1,"")) The "1s" occur in clusters marking the beginning and end of either a buy cycle OHLC up and above the 21d moving average, or a sell cycle below it. But I need to know on the fly how many cycles there have been, and what the duration of each cycle was so I can be more precise about when I execute a trade. The next thing I did was place in W9: =COUNT(W10:W274) This gives me the total number of occurances of where a row number pops up in the column. Since there will always be two for each beginning and end of a cycle, dividing that number by two tells me how many cycles there have been for the issue under study, and for the time frame covered. Since the row numbers progressively increase and are always unique, I then use the LARGE function to produce my subtotals. With this hack I accidentally did something convenient, as all the subtotals are neatly grouped at the top of the page w/o having to sort. In Y10 on down to Y41 (there are typically not more than 21 cycles for the time fame I am working with): =IF(W9=2,SUM(LARGE(W11:W275,W9-1)-LARGE(W11:W275,W9)),"") =IF(W9=4,SUM(LARGE(W11:W275,W9-3)-LARGE(W11:W275,W9-2)),"") etc, working my way down, using the prioritizing feature of the LARGE function to subtract the larger ROW number from the lesser one in the pair of the start and finish of that particular cycle. Its a clumsy solution, but it was all I could come up with. Tx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=505199 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple If, Then formula for excel | Excel Discussion (Misc queries) | |||
Simple Formula (I thought) | Excel Worksheet Functions | |||
help please with simple formula | New Users to Excel | |||
Very simple, but difficult formula question | Excel Worksheet Functions | |||
simple formula | Excel Worksheet Functions |