ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Simple formula doesn't quite add up (https://www.excelbanter.com/excel-discussion-misc-queries/3478-simple-formula-doesnt-quite-add-up.html)

circeo

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

RagDyer

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


circeo

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



RagDyer

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





All times are GMT +1. The time now is 07:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com