Applying a formula to a column

  • Thread starter Thread starter Andrew Clark
  • Start date Start date
A

Andrew Clark

Hello,

Say I have a column that I want to work on with a formula. I want to take
each value from the column, apply the formula to it, and total the result
in a footer cell for the column. Example: subtract 3 and multiply by 1/2

1
2
3
4

will produce -1 + -1/2 + 0 + 1/2 = -1. I would like to do this without a
hidden column to hold the intermediate results of applying the function
to each cell - so this is NOT what I want:

1 -1
2 -0.5
3 0
4 0.5

and sum the intermediate column to find the answer. Is there a way to do
this? I'm using Excel 2002

Thanks,
Andrew
 
Andrew

you can use array formula which can be entered as below
=SUM(((A1:A10)-3)*0.5)

but take care of pressing shift+control+enter when you use this formula
so that excel will treat this as array formula
after this formula will look like
{=SUM(((A1:A10)-3)*0.5)}

Regards
NC
 
Will this do what you want?
=(SUM(D4:D8)-(COUNT(D4:D8)*3))/2

--
Ken Russell

(e-mail address removed)
Remove yourhat to reply by e-mail
..
 
Here are two similar ideas. This is an array formula...(Ctrl-Shift-Enter)

=SUM(A1:A4-3)/2

and this is a non-array entered formula...
=SUMPRODUCT(A1:A4-3)/2

HTH
 
Andrew

you can use array formula which can be entered as below
=SUM(((A1:A10)-3)*0.5)

but take care of pressing shift+control+enter when you use this formula
so that excel will treat this as array formula
after this formula will look like
{=SUM(((A1:A10)-3)*0.5)}

Regards
NC
Click to expand...

That works beautifully! Thank you, I never new how to do it.

Andrew
 
Back
Top