Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 10th 05, 10:35 PM
circeo
 
Posts: n/a
Default 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   Report Post  
Old January 11th 05, 02:30 AM
RagDyer
 
Posts: n/a
Default

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   Report Post  
Old January 17th 05, 08:15 PM
circeo
 
Posts: n/a
Default

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   Report Post  
Old January 17th 05, 09:04 PM
RagDyer
 
Posts: n/a
Default

Thanks for the feed-back.
--


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula displays does not calculate Neil Bhandar Excel Discussion (Misc queries) 4 January 10th 05 10:55 PM
how do I make a formula NOT change when the data range is moved? Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 09:02 PM
Creating Formula using check boxes Anthony Slater Excel Discussion (Misc queries) 3 January 4th 05 03:03 PM
Paste is is copying in formula, but display is wrong. Matt Excel Discussion (Misc queries) 2 December 7th 04 08:37 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 11:29 AM


All times are GMT +1. The time now is 10:29 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017