Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting and adding values int two different columns
I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some
assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting and adding values int two different columns
You don't have to look for commas in your formula. Since you are looking for
only the numbers 1 to 5 just look for these numbers and nothing else. You could replace Search with FIND which will also reduce the number of characters. =IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$8)),'Alternative 1'!$G$8)+IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$9)),'Alternative 1'!$G$9,0)+IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$10)),'Alternative 1'!$G$10) "migpics" wrote: I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting and adding values int two different columns
Thanks Joel. The final version will go all the way to 100 years so I need to
make sure there is some way it doesn't count all the one's for example if the number 1 is in the number 10 it would be thrown off. Thanks! Miguel "Joel" wrote: You don't have to look for commas in your formula. Since you are looking for only the numbers 1 to 5 just look for these numbers and nothing else. You could replace Search with FIND which will also reduce the number of characters. =IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$8)),'Alternative 1'!$G$8)+IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$9)),'Alternative 1'!$G$9,0)+IF(ISNUMBER(SEARCH(A2,'Alternative 1'!$F$10)),'Alternative 1'!$G$10) "migpics" wrote: I'm developing a basic Life Cycle Cost Analysis Spreadsheet and need some assistance adding the costs of particular activity into a specified year. Example: This column checks if the Cash Flow Year is listed in the "Year Cost Occurs" and and then adds it to the other rows that apply for a total cash flow year. Cost$ Year Cost Occurs | Cash Flow Year Total in Cash Flow Year 300 0,2,4,5 | 0 =330+225 225 0,1,3,5 | 1 =225+125 125 1,2,3,4,5 | 2 =125+300 3 =225+125 4 =300+125 5 =300+225+125 I am hoping someone can have some code that will look at the Year cost occurs and then add it to the Total in Cash Flow Year. Currently, this is the code I use =IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$8&",")),'Alternative 1'!$G$8, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$9&",")),'Alternative 1'!$G$9, 0)+IF(ISNUMBER(SEARCH(","&A2&",",","&'Alternative 1'!$F$10&",")),'Alternative 1'!$G$10, 0) It applies to two different sheets but the lenght of the string is limited so my formula is basically too long. Any suggestions to create a loop through an array function? Thanks Miguel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting and adding values int two different columns
Why not set it up in your spreadsheet this way?
Your set up seems so complex. And adding 100 years seems like it will be too many characters for your formula. A B C D E F Year Cost Cost 2 Cost 3 Total 0 2000 225 300 525 =sum(C1:E1) 1 2001 125 225 350 2 2002 125 300 425 3 2003 125 225 350 4 2004 125 300 425 5 2005 125 225 300 650 6 2006 7 2007 8 2008 9 2009 10 2010 11 2011 12 2012 13 2013 14 2014 15 2015 16 2016 Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting and adding values int two different columns
Thanks Dan,
The reason I don't want to set it up that way is because I want people just to be able to input event dates on a separate sheet and the cash flow diagram to be updated. Thanks! Miguel "dan dungan" wrote: Why not set it up in your spreadsheet this way? Your set up seems so complex. And adding 100 years seems like it will be too many characters for your formula. A B C D E F Year Cost Cost 2 Cost 3 Total 0 2000 225 300 525 =sum(C1:E1) 1 2001 125 225 350 2 2002 125 300 425 3 2003 125 225 350 4 2004 125 300 425 5 2005 125 225 300 650 6 2006 7 2007 8 2008 9 2009 10 2010 11 2011 12 2012 13 2013 14 2014 15 2015 16 2016 Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Values Across Columns | Setting up and Configuration of Excel | |||
Counting Rows Then Counting Values in Columns | Excel Programming | |||
Counting columns, adding totals #2 | Excel Programming | |||
Counting columns, adding totals #2 | Excel Programming | |||
Counting columns, adding totals | Excel Programming |