Is there a formula that allows me to sum the values in a variable Range,
whereby a fixed amount is subtracted from each value in the Range?

e.g. Column A contains the following data:

A1 Amount
A2 10
A3 -5

The value of cell B1 has to be subtracted from each value in the A
range.
B1 contains 1.

So the expected value should be: 9 + (-6) = 3

Using the (array) formula
-{=SUM(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)-$B$1)} - produces the
unexpected answer -2! The non-array version produces a #VALUE error.


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=376699

Try:

=SUM(A:A)-COUNTA(A:A)*$B$1

HTH

"rvExcelNewTip" wrote:


Is there a formula that allows me to sum the values in a variable Range,
whereby a fixed amount is subtracted from each value in the Range?

e.g. Column A contains the following data:

A1 Amount
A2 10
A3 -5

The value of cell B1 has to be subtracted from each value in the A
range.
B1 contains 1.

So the expected value should be: 9 + (-6) = 3

Using the (array) formula
-{=SUM(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)-$B$1)} - produces the
unexpected answer -2! The non-array version produces a #VALUE error.


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=376699

Sorry .. to allow for header ...

=SUM(A:A)-(COUNTA(A:A)-1)*$B$1


"Toppers" wrote:

Try:

=SUM(A:A)-COUNTA(A:A)*$B$1

HTH

"rvExcelNewTip" wrote:


Is there a formula that allows me to sum the values in a variable Range,
whereby a fixed amount is subtracted from each value in the Range?

e.g. Column A contains the following data:

A1 Amount
A2 10
A3 -5

The value of cell B1 has to be subtracted from each value in the A
range.
B1 contains 1.

So the expected value should be: 9 + (-6) = 3

Using the (array) formula
-{=SUM(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)-$B$1)} - produces the
unexpected answer -2! The non-array version produces a #VALUE error.


--
rvExcelNewTip
------------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
View this thread: http://www.excelforum.com/showthread...hreadid=376699

thanks folks! basic algebra, I should have thought of

--
rvExcelNewTi
-----------------------------------------------------------------------
rvExcelNewTip's Profile: http://www.excelforum.com/member.php...fo&userid=1566
View this thread: http://www.excelforum.com/showthread.php?threadid=37669

I don't know why, but my first post didn't come through. Try the same
as Toppers but account for the "Amount" header cell:

=SUM(A:A)-(COUNTA(A:A)-1)*$B$1

Your formula is basically of the format:
(A-X) + (B-X) + (C-X) + ... + (n-X),
where n is the # rows and X = the value in B1

This is equivalent to:
A + B + C + ... + n - nX

So the cell formula is:
=SUM(A:A)-(COUNTA(A:A)-1)*B1

Take the sum of column A, subtract from it the product of the count of
non-blank cells in column A times the value in B1. Note the -1 in
COUNT(A:A)-1 is to account for the header cell "Amount".

A simple formula:
=SUM(A1:A2)-COUNT(A1:A2)*(B1)

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376699