|
Create a running totaol
CELL A1 has a number CELL B1 is the total (A1). As I add a number in CELL A2 I want CELL B2 to be the running total. Then I add a number to CELL A3 and I want B3 to be the total and etc. I don't want the cells in B to have any number in them untill I add something in column A. Please help. THANKS!! -- Jdog ------------------------------------------------------------------------ Jdog's Profile: http://www.excelforum.com/member.php...o&userid=28433 View this thread: http://www.excelforum.com/showthread...hreadid=480321 |
Create a running totaol
Try this for a non-volatile suggestion:
=IF(A1,SUM($A$1:A1),"") -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Jdog" wrote in message ... CELL A1 has a number CELL B1 is the total (A1). As I add a number in CELL A2 I want CELL B2 to be the running total. Then I add a number to CELL A3 and I want B3 to be the total and etc. I don't want the cells in B to have any number in them untill I add something in column A. Please help. THANKS!! -- Jdog ------------------------------------------------------------------------ Jdog's Profile: http://www.excelforum.com/member.php...o&userid=28433 View this thread: http://www.excelforum.com/showthread...hreadid=480321 |
Create a running totaol
"RagDyeR" wrote in message
... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
Meant to copy down. Look again
$a$1:a1 -- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
Thank you Don.
You can be my lawyer any time you wish!<bg -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 -- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
It would have to be copied down in order to fulfill the OP's request of
being a *running* total. And, of course ... I always follow OP directives.<g As far as volatility ... 4,000 rows being calculated every time *anything* in the sheet changes, is really not even a point of discussion. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
Don Guillett" wrote in message
... Meant to copy down. Look again $a$1:a1 So it does. My apologies to RagDyeR -- Sandy Replace@mailinator with @tiscali.co.uk "Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 -- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
"Sandy Mann" wrote in message
... Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 So it does. My apologies to RagDyeR Or should I say my apologies to your client <g -- Sandy Replace@mailinator with @tiscali.co.uk "Sandy Mann" wrote in message ... Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 So it does. My apologies to RagDyeR -- Sandy Replace@mailinator with @tiscali.co.uk "Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 -- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
OK. One billable second. If I was a lawyer I would have to charge for an
hour. -- Don Guillett SalesAid Software "RagDyeR" wrote in message ... Thank you Don. You can be my lawyer any time you wish!<bg -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 -- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
Billable ? ... Charge ? ... JA nie rozumiem! ! !
-- Dobry Dzien, Ryszard Dormack --------------------------------------------------------------------------- Sprawiac przyjemnosc (prosze) utrzymuja cala zgodnosc w obrebie *NewsGroup*, tak (wiec) caly moze przynosic korzysc ! <vbg --------------------------------------------------------------------------- "Don Guillett" wrote in message ... OK. One billable second. If I was a lawyer I would have to charge for an hour. -- Don Guillett SalesAid Software "RagDyeR" wrote in message ... Thank you Don. You can be my lawyer any time you wish!<bg -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "Don Guillett" wrote in message ... Meant to copy down. Look again $a$1:a1 -- Don Guillett SalesAid Software "Sandy Mann" wrote in message ... "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. -- Regards Sandy Replace@mailinator with @tiscali.co.uk |
Create a running totaol
The problem I have with this solution is that if you work with the
results of the running total you get #value because of the "" value. Sandy Mann wrote: "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. |
Create a running totaol
The OP asked that "no number" be present until a value was entered in Column
A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "noyb" wrote in message ... The problem I have with this solution is that if you work with the results of the running total you get #value because of the "" value. Sandy Mann wrote: "RagDyeR" wrote in message ... Try this for a non-volatile suggestion: =IF(A1,SUM($A$1:A1),"") I assume that you mean to copy the formula down or you meant =IF(A1,SUM(A:A),"") which would be fine if there was no other numbers in column A. If you meant copy down and the OP had say, 4,000 rows of data, would that not be more of a calculation load than a volatile function? Of course it would depend on what the rest of the sheet was like. |
Create a running totaol
My apologies, I was actually reacting generally to the fact that Excel
does not handle the results of a formula which resolves to "" very well and not to this specific question. Ragdyer wrote: The OP asked that "no number" be present until a value was entered in Column A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". |
Create a running totaol
Well ... reacting "generally" ... If you attempted to add (Sum() ), the
running total column that contained those zero length strings ( "" ), you would get an accurate total, with *no* error messages. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "noyb" wrote in message ... My apologies, I was actually reacting generally to the fact that Excel does not handle the results of a formula which resolves to "" very well and not to this specific question. Ragdyer wrote: The OP asked that "no number" be present until a value was entered in Column A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". |
Create a running totaol
Hi /Torsorq,
You might want to reconsider your requirements after reading http://groups.google.com/groups?as_u...2@TK2MSFTNGP10 Slow Response, Memory Problems, and Speeding up Excel http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "RagDyeR" wrote in message ... Well ... reacting "generally" ... If you attempted to add (Sum() ), the running total column that contained those zero length strings ( "" ), you would get an accurate total, with *no* error messages. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "noyb" wrote in message ... My apologies, I was actually reacting generally to the fact that Excel does not handle the results of a formula which resolves to "" very well and not to this specific question. Ragdyer wrote: The OP asked that "no number" be present until a value was entered in Column A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". |
Create a running totaol
BTW, who's Torsorq?<g
Anyway, very enlightening David. When you come right down to it, it's really quite logical that =Sum($A$1:A1) Does really create a monster calculation job. One doesn't think about it when referencing several hundred rows. I don't think I'll suggest it again ... at least not without a caveat as to the size of the calculation. However, one might try this: =INDEX($B$1:$B$20000,ROW()-1)+A2 In place of this: =OFFSET(B2,-1,)+A2 As far as I can tell, they're about the same speed ... for calculation ... as well as deletion. But I must admit, that the Offset does "look" cleaner then the Index, Just as the =Sum($A$1:A1) Looks cleaner then the Offset. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "David McRitchie" wrote in message ... Hi /Torsorq, You might want to reconsider your requirements after reading http://groups.google.com/groups?as_u...2@TK2MSFTNGP10 Slow Response, Memory Problems, and Speeding up Excel http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "RagDyeR" wrote in message ... Well ... reacting "generally" ... If you attempted to add (Sum() ), the running total column that contained those zero length strings ( "" ), you would get an accurate total, with *no* error messages. -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "noyb" wrote in message ... My apologies, I was actually reacting generally to the fact that Excel does not handle the results of a formula which resolves to "" very well and not to this specific question. Ragdyer wrote: The OP asked that "no number" be present until a value was entered in Column A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". |
Create a running totaol
Guess I picked the name from the wrong thread in a previous message.
Appears that one involved counting instead of a total. Why would index work faster or make more sense than offset of one row up from a cell on the current row. "RagDyer" wrote in message ... BTW, who's Torsorq?<g Anyway, very enlightening David. When you come right down to it, it's really quite logical that =Sum($A$1:A1) Does really create a monster calculation job. One doesn't think about it when referencing several hundred rows. I don't think I'll suggest it again ... at least not without a caveat as to the size of the calculation. However, one might try this: =INDEX($B$1:$B$20000,ROW()-1)+A2 In place of this: =OFFSET(B2,-1,)+A2 As far as I can tell, they're about the same speed ... for calculation ... as well as deletion. But I must admit, that the Offset does "look" cleaner then the Index, Just as the =Sum($A$1:A1) Looks cleaner then the Offset. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "David McRitchie" wrote in message ... Hi /Torsorq, You might want to reconsider your requirements after reading http://groups.google.com/groups?as_u...2@TK2MSFTNGP10 Slow Response, Memory Problems, and Speeding up Excel http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "RagDyeR" wrote in message ... Well ... reacting "generally" ... If you attempted to add (Sum() ), the running total column that contained those zero length strings ( "" ), you would get an accurate total, with *no* error messages. -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "noyb" wrote in message ... My apologies, I was actually reacting generally to the fact that Excel does not handle the results of a formula which resolves to "" very well and not to this specific question. Ragdyer wrote: The OP asked that "no number" be present until a value was entered in Column A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". |
Create a running totaol
Never said it would be faster or better, just an observation of an
alternative approach. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "David McRitchie" wrote in message ... Guess I picked the name from the wrong thread in a previous message. Appears that one involved counting instead of a total. Why would index work faster or make more sense than offset of one row up from a cell on the current row. "RagDyer" wrote in message ... BTW, who's Torsorq?<g Anyway, very enlightening David. When you come right down to it, it's really quite logical that =Sum($A$1:A1) Does really create a monster calculation job. One doesn't think about it when referencing several hundred rows. I don't think I'll suggest it again ... at least not without a caveat as to the size of the calculation. However, one might try this: =INDEX($B$1:$B$20000,ROW()-1)+A2 In place of this: =OFFSET(B2,-1,)+A2 As far as I can tell, they're about the same speed ... for calculation .... as well as deletion. But I must admit, that the Offset does "look" cleaner then the Index, Just as the =Sum($A$1:A1) Looks cleaner then the Offset. -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "David McRitchie" wrote in message ... Hi /Torsorq, You might want to reconsider your requirements after reading http://groups.google.com/groups?as_u...2@TK2MSFTNGP10 Slow Response, Memory Problems, and Speeding up Excel http://www.mvps.org/dmcritchie/excel/slowresp.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "RagDyeR" wrote in message ... Well ... reacting "generally" ... If you attempted to add (Sum() ), the running total column that contained those zero length strings ( "" ), you would get an accurate total, with *no* error messages. -- Regards, RD -------------------------------------------------------------------------- -- ------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- -- ------------------- "noyb" wrote in message ... My apologies, I was actually reacting generally to the fact that Excel does not handle the results of a formula which resolves to "" very well and not to this specific question. Ragdyer wrote: The OP asked that "no number" be present until a value was entered in Column A. What are your specs? Can you accept a zero? =IF(A1,SUM($A$1:A1),0) Also, exactly what formula are you using, and in what way are you using it, that's giving you an error? There might be viable "workarounds". |
Create a running totaol
Many great ideas!! I want to thank everyone for thier comments and suggestions. -- Jdog ------------------------------------------------------------------------ Jdog's Profile: http://www.excelforum.com/member.php...o&userid=28433 View this thread: http://www.excelforum.com/showthread...hreadid=480321 |
| All times are GMT +1. The time now is 08:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com