Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations using the last available figure in a column
I have a spreadsheet with data in colum A:D rows 5:24
Lets say they're items in a list. sometimes the list has more itmes than others. Description Cash IN Cash Out Running Balance If there is nothing in either the Cash In or Cash Out cells then therre will be no running balance. for thaqt line. The last balance figure is the running balance. In row 25 I have different data that is sometimes there buy not always (But it's always in row 25). Let's call that Overhead I want to be able to get the earlier running balance figure (from somwherw above) and Add the Cash In Figure (B25) or subtract the Cash Out figure (C25) (if any) and update the Running Balance figure accordingly. I use this formula to post the running balance figure if there is a plus or minus: =IF((B7="")*AND(C7=""),"",D6+B7-C7) -- (Example shows row 7) It displays as blank if there is no changes to the balance figure above. If I copy the same formula to row 25...I get a #VALUE! error when I put data in row 25. So, how do I get the last available figure in the column and do a calculation on it in row 25? Thanks for any suggestions. Jon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations using the last available figure in a column
try the following
=IF((B7="")*AND(C7=""),"",SUM(D6,B7,-C7)) the sum function will ignor the text that was giving you the problem and still allow you to add and subtract at least it seems to in my sample David "jonco" wrote: I have a spreadsheet with data in colum A:D rows 5:24 Lets say they're items in a list. sometimes the list has more itmes than others. Description Cash IN Cash Out Running Balance If there is nothing in either the Cash In or Cash Out cells then therre will be no running balance. for thaqt line. The last balance figure is the running balance. In row 25 I have different data that is sometimes there buy not always (But it's always in row 25). Let's call that Overhead I want to be able to get the earlier running balance figure (from somwherw above) and Add the Cash In Figure (B25) or subtract the Cash Out figure (C25) (if any) and update the Running Balance figure accordingly. I use this formula to post the running balance figure if there is a plus or minus: =IF((B7="")*AND(C7=""),"",D6+B7-C7) -- (Example shows row 7) It displays as blank if there is no changes to the balance figure above. If I copy the same formula to row 25...I get a #VALUE! error when I put data in row 25. So, how do I get the last available figure in the column and do a calculation on it in row 25? Thanks for any suggestions. Jon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations using the last available figure in a column
Assume D1 has the beginning balance and entries start in row 2.
Enter In D2:=if(And($B2="",$C2=""),"",$D$1+Sum($B$2:$B2)-Sum($C$2:$C2)) Then fill down. this would result in: D7: =if(And($B7="",$C7=""),"",$D$1+Sum($B$2:$B7)-Sum($C$2:$C7)) -- Regards, Tom Ogilvy "jonco" wrote in message . net... I have a spreadsheet with data in colum A:D rows 5:24 Lets say they're items in a list. sometimes the list has more itmes than others. Description Cash IN Cash Out Running Balance If there is nothing in either the Cash In or Cash Out cells then therre will be no running balance. for thaqt line. The last balance figure is the running balance. In row 25 I have different data that is sometimes there buy not always (But it's always in row 25). Let's call that Overhead I want to be able to get the earlier running balance figure (from somwherw above) and Add the Cash In Figure (B25) or subtract the Cash Out figure (C25) (if any) and update the Running Balance figure accordingly. I use this formula to post the running balance figure if there is a plus or minus: =IF((B7="")*AND(C7=""),"",D6+B7-C7) -- (Example shows row 7) It displays as blank if there is no changes to the balance figure above. If I copy the same formula to row 25...I get a #VALUE! error when I put data in row 25. So, how do I get the last available figure in the column and do a calculation on it in row 25? Thanks for any suggestions. Jon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations using the last available figure in a column
True, but how does it accumulate the total if the previous total (D6 for
example) is "" Just a thought for the OP. -- Regards, Tom Ogilvy "dkinn" wrote in message ... try the following =IF((B7="")*AND(C7=""),"",SUM(D6,B7,-C7)) the sum function will ignor the text that was giving you the problem and still allow you to add and subtract at least it seems to in my sample David "jonco" wrote: I have a spreadsheet with data in colum A:D rows 5:24 Lets say they're items in a list. sometimes the list has more itmes than others. Description Cash IN Cash Out Running Balance If there is nothing in either the Cash In or Cash Out cells then therre will be no running balance. for thaqt line. The last balance figure is the running balance. In row 25 I have different data that is sometimes there buy not always (But it's always in row 25). Let's call that Overhead I want to be able to get the earlier running balance figure (from somwherw above) and Add the Cash In Figure (B25) or subtract the Cash Out figure (C25) (if any) and update the Running Balance figure accordingly. I use this formula to post the running balance figure if there is a plus or minus: =IF((B7="")*AND(C7=""),"",D6+B7-C7) -- (Example shows row 7) It displays as blank if there is no changes to the balance figure above. If I copy the same formula to row 25...I get a #VALUE! error when I put data in row 25. So, how do I get the last available figure in the column and do a calculation on it in row 25? Thanks for any suggestions. Jon |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations using the last available figure in a column
Another suggestion (but not quite as short as Tom's) - you could use this in
your formula in D2 and copy down. It should pull out the last number. Lookup(max($D$1:$d1)+1,$d$1:$d1) "jonco" wrote: I have a spreadsheet with data in colum A:D rows 5:24 Lets say they're items in a list. sometimes the list has more itmes than others. Description Cash IN Cash Out Running Balance If there is nothing in either the Cash In or Cash Out cells then therre will be no running balance. for thaqt line. The last balance figure is the running balance. In row 25 I have different data that is sometimes there buy not always (But it's always in row 25). Let's call that Overhead I want to be able to get the earlier running balance figure (from somwherw above) and Add the Cash In Figure (B25) or subtract the Cash Out figure (C25) (if any) and update the Running Balance figure accordingly. I use this formula to post the running balance figure if there is a plus or minus: =IF((B7="")*AND(C7=""),"",D6+B7-C7) -- (Example shows row 7) It displays as blank if there is no changes to the balance figure above. If I copy the same formula to row 25...I get a #VALUE! error when I put data in row 25. So, how do I get the last available figure in the column and do a calculation on it in row 25? Thanks for any suggestions. Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting a column read figure from table | Excel Worksheet Functions | |||
Can't figure out a certain column sort | Excel Discussion (Misc queries) | |||
column is added up by original figure not after rounding up | New Users to Excel | |||
1-7 Columns, to find last column with figure in | Excel Worksheet Functions | |||
Freezing the data in a column and moving the calculations to the next column | Excel Programming |