| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running Sum across columns but only to a certain point
I have a some values in three columns that basically snake from Day 1 to Day
45 and I will like to add them up starting from Day 1 going to a particular Day that adds up to a value in another field. Day1 $100,000.00 Day21 Day41 Day2 Day22 Day42 Day3 $5,000.00 Day23 Day43 Day4 $(5,000.00) Day24 Day44 Day5 $57,000.00 Day25 Day45 Day6 $80,000.00 Day26 Day46 Day7 $- Day27 Day47 Day8 $46,000.00 Day28 Day48 Day9 $- Day29 Day49 Day10 $- Day30 Day50 Day11 $80,000.00 Day31 Day51 Day12 $90,000.00 Day32 Day52 Day13 $10,000.00 Day33 Day53 Day14 Day34 Day54 Day15 Day35 Day55 Day16 Day36 Day56 Day17 Day37 Day57 Day18 Day38 Day58 Day19 Day39 Day59 Day20 Day40 Day60 what I want to return is the day in which it finally meets that particular value. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Running Sum across columns but only to a certain point
Try the following, which uses a defined name and three helper cells...
Assumptions: A2:F21 contains the data Columns A, C, and E contain the day Columns B, D, and F contain the corresponding values H2 contains the value of interest Defined name: Insert Name Define Name: BigNum Refers to: =9.99999999999999E+307 Click Ok Formulas: I2: =IF(N(H2),MATCH(TRUE,SUM(B2:B21)+SUM(D2:D21)+SUBTO TAL(9,OFFSET(F2:F21,,,R OW(F2:F21)-ROW(F2)+1))=H2,0),"") ....confirmed with CONTROL+SHIFT+ENTER J2: =IF(N(H2),MATCH(TRUE,SUM(B2:B21)+SUBTOTAL(9,OFFSET (D2:D21,,,ROW(D2:D21)-R OW(D2)+1))=H2,0),"") ....confirmed with CONTROL+SHIFT+ENTER K2: =IF(N(H2),MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:B21,,,RO W(B2:B21)-ROW(B2)+1))= H2,0),"") ....confirmed with CONTROL+SHIFT+ENTER L2: =IF(N(H2),INDEX((A2:A21,C2:C21,E2:E21),LOOKUP(BigN um,I2:K2),,3-MATCH(BigN um,I2:K2)+1),"") ....confirmed with just ENTER Hope this helps! In article , Steven Cheng wrote: I have a some values in three columns that basically snake from Day 1 to Day 45 and I will like to add them up starting from Day 1 going to a particular Day that adds up to a value in another field. Day1 $100,000.00 Day21 Day41 Day2 Day22 Day42 Day3 $5,000.00 Day23 Day43 Day4 $(5,000.00) Day24 Day44 Day5 $57,000.00 Day25 Day45 Day6 $80,000.00 Day26 Day46 Day7 $- Day27 Day47 Day8 $46,000.00 Day28 Day48 Day9 $- Day29 Day49 Day10 $- Day30 Day50 Day11 $80,000.00 Day31 Day51 Day12 $90,000.00 Day32 Day52 Day13 $10,000.00 Day33 Day53 Day14 Day34 Day54 Day15 Day35 Day55 Day16 Day36 Day56 Day17 Day37 Day57 Day18 Day38 Day58 Day19 Day39 Day59 Day20 Day40 Day60 what I want to return is the day in which it finally meets that particular value. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Running Sum across columns but only to a certain point
Thanks Domenic....looks like a complicated formula....maybe I should have
just created a function for this instead... "Domenic" wrote: Try the following, which uses a defined name and three helper cells... Assumptions: A2:F21 contains the data Columns A, C, and E contain the day Columns B, D, and F contain the corresponding values H2 contains the value of interest Defined name: Insert Name Define Name: BigNum Refers to: =9.99999999999999E+307 Click Ok Formulas: I2: =IF(N(H2),MATCH(TRUE,SUM(B2:B21)+SUM(D2:D21)+SUBTO TAL(9,OFFSET(F2:F21,,,R OW(F2:F21)-ROW(F2)+1))=H2,0),"") ....confirmed with CONTROL+SHIFT+ENTER J2: =IF(N(H2),MATCH(TRUE,SUM(B2:B21)+SUBTOTAL(9,OFFSET (D2:D21,,,ROW(D2:D21)-R OW(D2)+1))=H2,0),"") ....confirmed with CONTROL+SHIFT+ENTER K2: =IF(N(H2),MATCH(TRUE,SUBTOTAL(9,OFFSET(B2:B21,,,RO W(B2:B21)-ROW(B2)+1))= H2,0),"") ....confirmed with CONTROL+SHIFT+ENTER L2: =IF(N(H2),INDEX((A2:A21,C2:C21,E2:E21),LOOKUP(BigN um,I2:K2),,3-MATCH(BigN um,I2:K2)+1),"") ....confirmed with just ENTER Hope this helps! In article , Steven Cheng wrote: I have a some values in three columns that basically snake from Day 1 to Day 45 and I will like to add them up starting from Day 1 going to a particular Day that adds up to a value in another field. Day1 $100,000.00 Day21 Day41 Day2 Day22 Day42 Day3 $5,000.00 Day23 Day43 Day4 $(5,000.00) Day24 Day44 Day5 $57,000.00 Day25 Day45 Day6 $80,000.00 Day26 Day46 Day7 $- Day27 Day47 Day8 $46,000.00 Day28 Day48 Day9 $- Day29 Day49 Day10 $- Day30 Day50 Day11 $80,000.00 Day31 Day51 Day12 $90,000.00 Day32 Day52 Day13 $10,000.00 Day33 Day53 Day14 Day34 Day54 Day15 Day35 Day55 Day16 Day36 Day56 Day17 Day37 Day57 Day18 Day38 Day58 Day19 Day39 Day59 Day20 Day40 Day60 what I want to return is the day in which it finally meets that particular value. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Running Sum across columns but only to a certain point
If you change the layout of your spreadsheet so that only two columns
contain the data, where Column A contains the date, and Column B contains the corresponding values, you could use the following formula instead... =IF(N(H2),INDEX(A2:A21,MATCH(TRUE,SUBTOTAL(9,OFFSE T(B2:B21,,,ROW(B2:B21)- ROW(B2)+1))=H2,0)),"") ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Steven Cheng wrote: Thanks Domenic....looks like a complicated formula....maybe I should have just created a function for this instead... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with grouping columns | New Users to Excel | |||
| Clearing information in certain columns | Excel Discussion (Misc queries) | |||
| Hiding columns and custom views problem | Excel Discussion (Misc queries) | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
| Counting the Contents of Two Columns | Excel Discussion (Misc queries) | |||
Linear Mode
