Level 1 (0 points)

Is there a way in which I can perform on an operation on a range within a function ?

For example, I have two columns - column A and column B. I am interested in computing the sumproduct(col A, col B) which is a straight forward function call.

Now, instead of computing sumproduct(col A, col B), I am interested in computing  sumproduct (col A - fixed value, col B) ie I want to subract a fixed value from each element of one of the columns. I say fixed value, but the value is essentially another cell in the table which is dynamically generated.

On straightforward solution is to create another column which represents col A - fixed value. Is there a way to do this without having to create a new column ?

If I've missed something obvious in searching for a solution to this, please can you just point me in the right direction ?

cheers,

Vikram

Macbook, Mac OS X (10.6.5)
• ###### 1. Re: Operation on ranges
Level 6 (13,615 points)

I think you will have to add an aux column as you proposed.

• ###### 2. Re: Operation on ranges
Level 6 (10,815 points)

You won't need an aux column for this particular result. Using columns A and B and the fixed value in cell C2,

SUMPRODUCT(A - C2,B) = SUMPRODUCT(A,B) - C2*SUM(B)