youtube boolean logic and array formula
- 10 years ago
Boolean logic is used in Array formula. In our example we are going to look at calculating the refunds with 3 criteria, Sales rep, Region and Date. Excel could perform this calculation by the use of the SUMIFS formula, however this is a very good example of how Boolean logic can be applied in an Array formula.
We begin this formula with =SUM as we want to get the total of the sale for the criteria. We then enter our first array, and this is the sales rep column and our logical test is to see if the sales rep equals the sales rep per the criteria, in this case, Mary. Lets just take the first 4 rows of data to discuss. We are going to get a False, True, False, False result as the first four sales reps are Kate, Mary, Joe and Joe. This will give us a 0,1,0,0.
We then enter our second array, and this is the region column and our logical test is to see if the region equals the region per the criteria, in this case, North. Lets just take the first 4 rows of data to discuss. We are going to get a True, True, False, False result as the first four sales reps are North, North, South and East. This will give us a 1,1,0,0.
We then multiply these results out. 0*1 = 0, 1*1=1, 0*0=0, 0*0=0. This leaves us with 0,1,0,0
We then enter our third array, and this is the date column and our logical test is to see if the date equals the date per the criteria, in this case, 01/01/2015. Lets just take the first 4 rows of data to discuss. We are going to get a True, True, True, False result as the first four sales reps are North, North, South and East. This will give us a 1,1,1,0.
We then multiply this result 1,1,1,0 against the result above 0,1,0,0 and this will leave us with 0,1,0,0
Finally, we get our last array, and this is the refund value. We do not have a logic test here, instead we are multiplying the value of the refund by the final result above of 0,1,0,0. This will give us 1345*0, 1025*1, 825*0, 1079*0. Our final answer here is 1025.
Watch this video to get a full understanding
We begin this formula with =SUM as we want to get the total of the sale for the criteria. We then enter our first array, and this is the sales rep column and our logical test is to see if the sales rep equals the sales rep per the criteria, in this case, Mary. Lets just take the first 4 rows of data to discuss. We are going to get a False, True, False, False result as the first four sales reps are Kate, Mary, Joe and Joe. This will give us a 0,1,0,0.
We then enter our second array, and this is the region column and our logical test is to see if the region equals the region per the criteria, in this case, North. Lets just take the first 4 rows of data to discuss. We are going to get a True, True, False, False result as the first four sales reps are North, North, South and East. This will give us a 1,1,0,0.
We then multiply these results out. 0*1 = 0, 1*1=1, 0*0=0, 0*0=0. This leaves us with 0,1,0,0
We then enter our third array, and this is the date column and our logical test is to see if the date equals the date per the criteria, in this case, 01/01/2015. Lets just take the first 4 rows of data to discuss. We are going to get a True, True, True, False result as the first four sales reps are North, North, South and East. This will give us a 1,1,1,0.
We then multiply this result 1,1,1,0 against the result above 0,1,0,0 and this will leave us with 0,1,0,0
Finally, we get our last array, and this is the refund value. We do not have a logic test here, instead we are multiplying the value of the refund by the final result above of 0,1,0,0. This will give us 1345*0, 1025*1, 825*0, 1079*0. Our final answer here is 1025.
Watch this video to get a full understanding