Home 
Search 
Today's Posts 
#1




Simple formula doesn't quite add up
I can't figure out a knotty problem that has been plaguing me for months.
=SUM(T3:X3)SUM(B3:P3,R3,S3,Y3) The above formula tallys a cash register. The simple arithmetic formula tallys all forms of income (receipts) including cash, checks, credit cards, gift certificates, etc. And then subtracts from the total the sum of all sales and services. The formula is loaded in column Z. The automatic calculation button is selected and the formula works perfectly until I reach the Y column and enter a value for gift certificate sales. As soon as I move off of the Y cell using either the arrows or the ENTER key... the Z cell "flashes" and the formula changes. Y is inserted at the end of the first calculation and the formula reads: =SUM(T3:Y3)SUM(B3:P3,R3,S3,Y3) Obviously, the tally is incorrect since the value in Y is now in BOTH sides of the equation. Any suggestions?  kc 
#2




Try this:
<Tools <Options <Edit tab, And *Uncheck* "Extend List Formats & Formulas".  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "circeo" wrote in message ... I can't figure out a knotty problem that has been plaguing me for months. =SUM(T3:X3)SUM(B3:P3,R3,S3,Y3) The above formula tallys a cash register. The simple arithmetic formula tallys all forms of income (receipts) including cash, checks, credit cards, gift certificates, etc. And then subtracts from the total the sum of all sales and services. The formula is loaded in column Z. The automatic calculation button is selected and the formula works perfectly until I reach the Y column and enter a value for gift certificate sales. As soon as I move off of the Y cell using either the arrows or the ENTER key... the Z cell "flashes" and the formula changes. Y is inserted at the end of the first calculation and the formula reads: =SUM(T3:Y3)SUM(B3:P3,R3,S3,Y3) Obviously, the tally is incorrect since the value in Y is now in BOTH sides of the equation. Any suggestions?  kc 
#3




Excellent! That was it. Thanks!
"RagDyer" wrote: Try this: <Tools <Options <Edit tab, And *Uncheck* "Extend List Formats & Formulas".  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "circeo" wrote in message ... I can't figure out a knotty problem that has been plaguing me for months. =SUM(T3:X3)SUM(B3:P3,R3,S3,Y3) The above formula tallys a cash register. The simple arithmetic formula tallys all forms of income (receipts) including cash, checks, credit cards, gift certificates, etc. And then subtracts from the total the sum of all sales and services. The formula is loaded in column Z. The automatic calculation button is selected and the formula works perfectly until I reach the Y column and enter a value for gift certificate sales. As soon as I move off of the Y cell using either the arrows or the ENTER key... the Z cell "flashes" and the formula changes. Y is inserted at the end of the first calculation and the formula reads: =SUM(T3:Y3)SUM(B3:P3,R3,S3,Y3) Obviously, the tally is incorrect since the value in Y is now in BOTH sides of the equation. Any suggestions?  kc 
#4




Thanks for the feedback.
 Regards, RD  Please keep all correspondence within the Group, so all may benefit!  "circeo" wrote in message ... Excellent! That was it. Thanks! "RagDyer" wrote: Try this: <Tools <Options <Edit tab, And *Uncheck* "Extend List Formats & Formulas".  HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "circeo" wrote in message ... I can't figure out a knotty problem that has been plaguing me for months. =SUM(T3:X3)SUM(B3:P3,R3,S3,Y3) The above formula tallys a cash register. The simple arithmetic formula tallys all forms of income (receipts) including cash, checks, credit cards, gift certificates, etc. And then subtracts from the total the sum of all sales and services. The formula is loaded in column Z. The automatic calculation button is selected and the formula works perfectly until I reach the Y column and enter a value for gift certificate sales. As soon as I move off of the Y cell using either the arrows or the ENTER key... the Z cell "flashes" and the formula changes. Y is inserted at the end of the first calculation and the formula reads: =SUM(T3:Y3)SUM(B3:P3,R3,S3,Y3) Obviously, the tally is incorrect since the value in Y is now in BOTH sides of the equation. Any suggestions?  kc 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Formula displays does not calculate  Excel Discussion (Misc queries)  
how do I make a formula NOT change when the data range is moved?  Excel Discussion (Misc queries)  
Creating Formula using check boxes  Excel Discussion (Misc queries)  
Paste is is copying in formula, but display is wrong.  Excel Discussion (Misc queries)  
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula?  Excel Discussion (Misc queries) 