• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Offset function with SumProduct

Guity


Offset(Range, Rows, Columns, [Height], [Width]) can either Make or Offset an existing range. It returns a range and so can be used as a range inside a Sumproduct or any other formula that requuires a Range


What do you want to do ?

Can you describe what your trying to achieve as there may be a dozen ways to do what you want or what we descibe may not do what you want.
 
Hui,

Sorry about my last answer. I get back from work very late and I can get to what I really like to spend my time. A few days ago, while I was reading the Excel Black-belt, this idea formed in mind that how we can use offset with a sum product. Since the author was indicating that offset is mainly used in conjunction with other functions. Basically, I don't have anything, but I want to learn offset function. When I look into function, I really know what it does, but I want to be able to apply it in real situation or getting an example of when we use offset function. For example: we use look up function, when we want to find a value in a series of values. What is the application of offset function?

Thank you for the help which is great. Guity
 
Offset is commonly used to create a dynamic range, that is one that is not statically defined, but grows (and shrinks) as the data grows and shrinks.


This is achieved by embedding a function within offset that counts how many items are in the data. For instance


=OFFSET($A$1,,,COUNTA($A:$A)-1,1)


will return an array of references column A for values. If you add more data to column A, then the array grows and so on. The COUNTA($A:$A) is counting the data in column A, 1 is being subtracted so as to not include a header.


This is especially useful in SUMPRODUCT, as this is an array processing function as so can be quite resource intensive, so keeping the range processed to the minimum is useful.


So a SUMPRODUCT formula such as


=SUMPRODUCT(--(LEFT($A:$A,5)="WHO54"),$B:$B)


which processes the whole column can be written as


=SUMPRODUCT(--(LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54"),OFFSET($B$1,,,COUNTA($A:$A)-1,1))


and avoid all of that processing. Note that even the offset for column B counts the items in column A, it is impoirtant to ensure all arrays are the same size.


having two similar COUNTA functions in the formula is not good, this is unnecessary processing. Far better to put


=COUNTA($A:$A)-1


in a spare cell, say M1, and use


=SUMPRODUCT(--(LEFT(OFFSET($A$1,,,$M$1,1),5)="WHO54"),OFFSET($B$1,,,$M$1,1))


so that only one cell gets recalculated on a change n column A.
 
Thank you for the response, I need some time to understand and absorb what you have said. I will get back to you as soon as I come to some result.

Regards,

Guity
 
This is the function of offset:

=offset(Range, Rows, columns, width, height)

comparing this =OFFSET($A$1,,,COUNTA($A:$A)-1,1) with what I have on the top: which part of function does CountA($A:$A)stand for?


-(LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54": You are using this part in a sum product function

This gives us a text value: "WHo54", which a string. In sum product function: we are multiplying the corresponding values in arrays and then sum them. How can we multiply a string value?


Can you please illustrate your example in a spread sheet?


Thank you for the help.

Guity
 
1.

=OFFSET($A$1,,,COUNTA($A:$A)-1,1) with what I have on the top: which part of function does CountA($A:$A)stand for?

=offset(Range, Rows, columns, width, height) is wrong

=offset(Range, Row Offset, Column Offset, Height, Width ) is correct

and so CountA($A:$A) is the height which is based on the number of entries in Column A -1


2. It says if the left 5 characters of each cell in OFFSET($A$1,,,$M$1,1), then multiply it by the values in OFFSET($B$1,,,COUNTA($A:$A)-1,1) and sum it up.
 
It is a string, it is a Boolean result, because you are comparing cell values to a specific text value, which will always be True or False.


See http://xldynamic.com/source/xld.SUMPRODUCT.html for a detailed explanation
 
-xld

I understood when I thought about it more. Hui (Excel Ninja) had explained about getting +1 or -- get a positive digit.


I know (LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54" returns:


either 0 or 1.


Are you doing this to show how to get 0 or 1 that on the next step, it multiplies by the value in OFFSET($B$1,,,COUNTA($A:$A)-1,1) then sum it up?

Thanks a lot.


Hui,

Thank you for your help. Is there anyway to show this to me on a spreadsheet?

Guity
 
<quote>I understood when I thought about it more. Hui (Excel Ninja) had explained about getting +1 or -- get a positive digit.


I know (LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54" returns:


either 0 or 1.</quote>


No it doesn't! It returns True or False, you need some way to coerce True/False to 1/0 as explained in that link I provided to you.


The second OFFSET was to provide a set of values to sum.
 
Guity

It is best to think about Sumproduct in terms of processing 2 or more arrays or ranges

The first array consists of LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54" and so for each cell in the range if the left 5 characters ="WHO54" it will evaluate to TRUE or FALSE


Lets use some data like

WHO551 10

WHO541 10

WHO563 10

WHO547 10

WHO541 10

WHO548 10

WHO568 10


Using the LEFT(OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)="WHO54" this will evaluate the first column to {False,True,False,True,True,True,False)


When this array is multiplied by +1* or -- it will be evaluated to {0,1,0,1,1,1,0)


and the Sumproduct will multiply each of these by the corresponding value from Column B

OFFSET($B$1,,,COUNTA($A:$A)-1,1)


ie:

0 x 10

1 x 10

0 x 10

1 x 10

1 x 10

1 x 10

0 x 10

and sum it up


Giving 40


I have setup an example at

http://rapidshare.com/files/433381352/GuitySumproduct.xlsx
 
Xld,

I read the link. Link says that we need to coerce true/ False to 1/0. It shows a bunch ways to coerce: as to raise to power 1 or add +0.

We know in excel true is equal to 1 and false is equal to 0. Why do we need to convert the true and false to numbers 1 and 0?

As the essay says: you can multiply true by true in excel and it gives 1. Why is there a need to coerce true and false to numbers?

Other than this concern on the top, I received what I wanted.

Regards,

Guity
 
Guity

True is only 1 when it is evaluated


Try this for proof


Put the numbers 1 to 10 in A1:A10


Put =sumproduct((a1:A10>5)) somewhere

you will get 0

But there are 5 numbers > 5 ?


Now change to =sumproduct(1*(a1:A10>5)) or =sumproduct(--(a1:A10>5))

you will get 5


True is only evaluated to 1 when it is used in an equation


If you are using sumproduct like

=sumproduct((a1:A10>5)*(B1:B10<10))

You don't need the +1* or -- as the evaluation takes places against the second array.

It is just good practice to leave the +1* or -- therein case you or someone else later removes the second constraint.
 
Actually, Excel treats any non-zero number as true, not just 1 (it is very lax). That is why you often see code like


=IF(COUNTIF(rng,condition),"one thing","something else")


because whatever COUNTIF returns as a result other than 0 it will take the first action.


If you have two conditions, you can multiply the two arrays of True/False to return an array of 1/0, or you can coerce each array using --. Dont' forget, SUMPRODUCT is SUMming the PRODUCTs, hence the name, so you don't need *, and it is less efficient to use it.


I disagree strongly with Hui about leaving multiple operators in a formula, it is bad practice IMO. I personally prefer the double unary, --, because it is more efficient, and mor logically conforms to the standard use of SUMPRODUCT. Although as that paper says, there are some cases where * must be used because of the structure of the conditions.
 
Hui & xld

Thank you for all your help. Every thins is clear for me.


-Hui

The example you gave me explains everything. I understand the SumProduct function very well.


-xld

I understood why we multiply by *1, because true is only evaluated to 1 when it is used in an equation.


-Hui & xld

OFFSET($A$1,,,COUNTA($A:$A)-1,1),5):

after range you have: , , , for row and column, what does the blank space say?


In regards to using * or , between arrays, that wouldn't be my concern.

Regards,

Guity
 
OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)

the 2 blanks ,,, mean don't offset the Range from the Reference $A$1

ie: Row Offset = 0, Column offset=0

The new Range size will start at A1 and be COUNTA($A:$A)-1,1) rows high and 5 Columns wide
 
<quote>In regards to using * or , between arrays, that wouldn't be my concern.</quote>


It would be, will be, if you are trying to evaluate all conditions using,.
 
Actually, we have been misleading you a bit with


OFFSET($A$1,,,COUNTA($A:$A)-1,1),5)


because if we subtract 1 fro the header row, the header row is likely row 1, so we should either start explicitly at A2


OFFSET($A$2,,,COUNTA($A:$A)-1,1),5)


or implicitly by offsetting the cell reference by 1 row


OFFSET($A$1,1,,COUNTA($A:$A)-1,1),5)


This is why it is probably always best to be explicit with arguments,never leave them to default and use


OFFSET($A$2,0,0,COUNTA($A:$A)-1,1),5)


or


OFFSET($A$1,1,0,COUNTA($A:$A)-1,1),5)
 
You must log in or register to reply here.
Back
Top