| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RUNNING TOTAL
RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND
RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL????????????????? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
I ENTERED THE FORMULA THAT YOU SUGGESTED AS OPPOSED TO THE SIMPLIFIED ONE I
HAD USED PREVIOUSLY---- AND PROMPTLY ENDED UP WITH THE SAME RESULT. IN OTHER WORDS, AS SOON AS I COPIED THE FORMULA DOWN THE COLUMN FOR ABOUT TEN MORE ROWS THE SAME AMOUNT APPEARED OVER AND OVER AGAIN ALL THE WAY DOWN. ALSO, YOUR FORMULA CREATED ANOTHER PROBLEM. THE RUNNING TOTAL (WHICH WAS "CURRENCY" WITH "2" DECIMAL PLACES) WOULD BE 1 CENT OFF ABOUT EVERY OTHER RUNNING TOTAL. ANY FURTHER SUGGESTIONS TO RESOLVE THESE NOW TWO PROBLEMS? (MY CAPS LOCK KEY IS STUCK ON). THANKS FOR TRYING TO HELP!!!!!!!!!!!!!!!!!!!! "Desert Piranha" wrote: LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
You obviously did not read Desert Piranha's first line :-)
-- Kind regards, Niek Otten "LOST IN EXCEL 2000" wrote in message ... I ENTERED THE FORMULA THAT YOU SUGGESTED AS OPPOSED TO THE SIMPLIFIED ONE I HAD USED PREVIOUSLY---- AND PROMPTLY ENDED UP WITH THE SAME RESULT. IN OTHER WORDS, AS SOON AS I COPIED THE FORMULA DOWN THE COLUMN FOR ABOUT TEN MORE ROWS THE SAME AMOUNT APPEARED OVER AND OVER AGAIN ALL THE WAY DOWN. ALSO, YOUR FORMULA CREATED ANOTHER PROBLEM. THE RUNNING TOTAL (WHICH WAS "CURRENCY" WITH "2" DECIMAL PLACES) WOULD BE 1 CENT OFF ABOUT EVERY OTHER RUNNING TOTAL. ANY FURTHER SUGGESTIONS TO RESOLVE THESE NOW TWO PROBLEMS? (MY CAPS LOCK KEY IS STUCK ON). THANKS FOR TRYING TO HELP!!!!!!!!!!!!!!!!!!!! "Desert Piranha" wrote: LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
This may be because your cell referencing is 'fixed'. Delete the '$' sign
before copying the formula over to the other rows. "Niek Otten" wrote: You obviously did not read Desert Piranha's first line :-) -- Kind regards, Niek Otten "LOST IN EXCEL 2000" wrote in message ... I ENTERED THE FORMULA THAT YOU SUGGESTED AS OPPOSED TO THE SIMPLIFIED ONE I HAD USED PREVIOUSLY---- AND PROMPTLY ENDED UP WITH THE SAME RESULT. IN OTHER WORDS, AS SOON AS I COPIED THE FORMULA DOWN THE COLUMN FOR ABOUT TEN MORE ROWS THE SAME AMOUNT APPEARED OVER AND OVER AGAIN ALL THE WAY DOWN. ALSO, YOUR FORMULA CREATED ANOTHER PROBLEM. THE RUNNING TOTAL (WHICH WAS "CURRENCY" WITH "2" DECIMAL PLACES) WOULD BE 1 CENT OFF ABOUT EVERY OTHER RUNNING TOTAL. ANY FURTHER SUGGESTIONS TO RESOLVE THESE NOW TWO PROBLEMS? (MY CAPS LOCK KEY IS STUCK ON). THANKS FOR TRYING TO HELP!!!!!!!!!!!!!!!!!!!! "Desert Piranha" wrote: LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
THERE IS NO '$' IN THE FORMULA. I CHANGED THE COLUMN FROM "CURRENCY" TO
"NUMBER" BUT IT STILL MADE NO DIFFERENCE. THANKS FOR TRYING. "sjoseph" wrote: This may be because your cell referencing is 'fixed'. Delete the '$' sign before copying the formula over to the other rows. "Niek Otten" wrote: You obviously did not read Desert Piranha's first line :-) -- Kind regards, Niek Otten "LOST IN EXCEL 2000" wrote in message ... I ENTERED THE FORMULA THAT YOU SUGGESTED AS OPPOSED TO THE SIMPLIFIED ONE I HAD USED PREVIOUSLY---- AND PROMPTLY ENDED UP WITH THE SAME RESULT. IN OTHER WORDS, AS SOON AS I COPIED THE FORMULA DOWN THE COLUMN FOR ABOUT TEN MORE ROWS THE SAME AMOUNT APPEARED OVER AND OVER AGAIN ALL THE WAY DOWN. ALSO, YOUR FORMULA CREATED ANOTHER PROBLEM. THE RUNNING TOTAL (WHICH WAS "CURRENCY" WITH "2" DECIMAL PLACES) WOULD BE 1 CENT OFF ABOUT EVERY OTHER RUNNING TOTAL. ANY FURTHER SUGGESTIONS TO RESOLVE THESE NOW TWO PROBLEMS? (MY CAPS LOCK KEY IS STUCK ON). THANKS FOR TRYING TO HELP!!!!!!!!!!!!!!!!!!!! "Desert Piranha" wrote: LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
I READ HIS FIRST LINE. OBVIOUSLY, YOU DIDN'T READ MY NEXT TO THE LAST LINE
ANSWER. "Niek Otten" wrote: You obviously did not read Desert Piranha's first line :-) -- Kind regards, Niek Otten "LOST IN EXCEL 2000" wrote in message ... I ENTERED THE FORMULA THAT YOU SUGGESTED AS OPPOSED TO THE SIMPLIFIED ONE I HAD USED PREVIOUSLY---- AND PROMPTLY ENDED UP WITH THE SAME RESULT. IN OTHER WORDS, AS SOON AS I COPIED THE FORMULA DOWN THE COLUMN FOR ABOUT TEN MORE ROWS THE SAME AMOUNT APPEARED OVER AND OVER AGAIN ALL THE WAY DOWN. ALSO, YOUR FORMULA CREATED ANOTHER PROBLEM. THE RUNNING TOTAL (WHICH WAS "CURRENCY" WITH "2" DECIMAL PLACES) WOULD BE 1 CENT OFF ABOUT EVERY OTHER RUNNING TOTAL. ANY FURTHER SUGGESTIONS TO RESOLVE THESE NOW TWO PROBLEMS? (MY CAPS LOCK KEY IS STUCK ON). THANKS FOR TRYING TO HELP!!!!!!!!!!!!!!!!!!!! "Desert Piranha" wrote: LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RUNNING TOTAL
When you say "Sales Tax" in Column B,
Is it a % that's then multiplied by Column A in Column C? If that's the case, to avoid penny or two differences, you should Round() your formula in Column C. =ROUND(A1*(1+B1),2) Then, in D1, enter: =IF(C1,SUM($C$1:C1),"") And copy down as needed. Post back if this running total will exceed 1000 rows. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "LOST IN EXCEL 2000" wrote in message ... I READ HIS FIRST LINE. OBVIOUSLY, YOU DIDN'T READ MY NEXT TO THE LAST LINE ANSWER. "Niek Otten" wrote: You obviously did not read Desert Piranha's first line :-) -- Kind regards, Niek Otten "LOST IN EXCEL 2000" wrote in message ... I ENTERED THE FORMULA THAT YOU SUGGESTED AS OPPOSED TO THE SIMPLIFIED ONE I HAD USED PREVIOUSLY---- AND PROMPTLY ENDED UP WITH THE SAME RESULT. IN OTHER WORDS, AS SOON AS I COPIED THE FORMULA DOWN THE COLUMN FOR ABOUT TEN MORE ROWS THE SAME AMOUNT APPEARED OVER AND OVER AGAIN ALL THE WAY DOWN. ALSO, YOUR FORMULA CREATED ANOTHER PROBLEM. THE RUNNING TOTAL (WHICH WAS "CURRENCY" WITH "2" DECIMAL PLACES) WOULD BE 1 CENT OFF ABOUT EVERY OTHER RUNNING TOTAL. ANY FURTHER SUGGESTIONS TO RESOLVE THESE NOW TWO PROBLEMS? (MY CAPS LOCK KEY IS STUCK ON). THANKS FOR TRYING TO HELP!!!!!!!!!!!!!!!!!!!! "Desert Piranha" wrote: LOST IN EXCEL 2000 Wrote: RETAIL VALUE IN COLUMN A1; SALES TAX IN COLUMN B1; SUBTOTAL IN COLUMN C1; AND RUNNING TOTAL IN COLUMN D1. RETAIL VALUE IN COLUMN A2; SALES TAX IN COLUMN B2; SUBTOTAL IN COLUMN C2; AND RUNNING TOTAL FORMULA IN COLUMN D2 WHICH SAYS: =SUM(D1,C2). THIS GIVES ME THE CORRECT ANSWER IN COLUMN D2. HOWEVER, WHEN I COPY THE FORMULA DOWN THE ROWS IN COLUMN D THE SAME AMOUNT REPEATS ITSELF OVER AND OVER AGAIN IN EVERY ROW. IN OTHER WORDS THE SAME RUNNING TOTAL THAT I GOT IN D2 ENDS UP GOING INTO D3,D4,D5,D6, ETC. I DON'T WANT THE CALCULATED RUNNING TOTAL TO SHOW IN D3 UNTIL I HAVE ENTERED THE SUBTOTAL IN C3. HOW DO I PREVENT THE RUNNING TOTAL FROM APPEARING OVER AND OVER AGAIN UNTIL I HAVE ENTERED THE SUBTOTAL?????????????????Hi Lost, Please don't type in all upper case. Try this in D2 and copy down, as far as you need. =IF(C2="","",(SUM(D1,C2))) -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=496002 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| % of running total in pivot table | Excel Discussion (Misc queries) | |||
| Getting a running total? | Excel Worksheet Functions | |||
| Pivot Table - Running Total Annualised | Excel Discussion (Misc queries) | |||
| timesheet with running total of overtime | Excel Worksheet Functions | |||
| keep a running total of my formula results after each (F9) | Excel Discussion (Misc queries) | |||
Hybrid Mode
