• 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.

I need idea on a simpler formula

F

fred

Member
Hi all,


In column A I have the total sum product of C to AU

Line one has all the names.

and A2 = (B2*C2)+(D2*E2)+(F2*G2)+(H2*I2)+(J2*K2)+(L2*M2)+...+(AT2*AU2)

A3 = (B3*C3)+(D3*E3)+(F3*G3)+...+(AT3*AU3)


is there is simpler way by line to do this without clicking each cell on line 2? I tried sumproduct but I think I have received a wrong answer during testing. Thank you very much.
 
Fred


You don't have to use the Brackets, as mathematical conventions will look after them for you. So you could do

=B2*C2+D2*E2+F2*G2+H2*I2+ ... +AT2*AU2


But thats not what your after


I'm not sure about simpler, but this is definately shorter

=SUMPRODUCT((B2:AT2)*MOD(COLUMN(B2:AT2)-1,2),(C2:AU2)*MOD(COLUMN(C2:AU2),2))

Copy down
 
WOW! This is freakishly awesome!! Thank you very much, Hui. It even works when my boss want to put a blank column between the formula cell and the data cells!!! I'd never come up with this.


I'm still hung over at the magic and significance of all the functions that you have put together.

1. Column(set of data): why column the set, when the result is 2 or 3

2. when you mod(column(set of data)) it just come up to 1. I know sumproduct((data set)*1) is just wrong but I still fail to see the significance.


Could you please kindly teach me how I should approach this? And the thought process I need to learn? thank you very much, Hui.
 
Think of the goal of SUMPRODUCT is to create 2 arrays with data like this:

B2__C2

D2__E2

F2__G2

etc.

(ignore the underscores, I just used for spacing). The 2 arrays need to be multiplied across, and then summed (which is how SUMPRODUCT works by definition)


Noticing that both columns use every other column. Another way to think of that is to take all the EVEN columns in first array, and all the ODD columns in seconds array. This is where the MOD(COLUMN(...)) functions come in.


COLUMN returns the column number of your data set, and the MOD returns the remainder after dividing by x (x in this case is 2). Thus, we either get 0 (aka false) or 1 (aka true). By manipulating the column numbers, Hui's formula says to take all the data from B2:AT2 where the column number is even (e.g., col B = 2. Subtract 1 to get 1. MOD(1,2) = 1 aka true) and multiply that by all the data from C2:Au2 where the column number is odd (e.g., col C = 3, MOD (3,2) = 1 aka true).


Chandoo explains the basics here:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


Hui's excellent formula expands on how to create the true/false arrays.
 
Thank you very much Luke! /Bow


My jaw is on the floor thinking this is so amazing that Excel can do this. And even more amazing that you guys can come up with it!


I'm going to put this on my bookmark for future reference. ;)
 
On reflection I knocked off another 25 characters


Try this: =SUMPRODUCT(B2:AT2*C2:AU2*(MOD(COLUMN(B2:AT2),2)=0))
 
Fred - Hui's formula is an elegant solution to your problem and these techniques (in this case, distinguishing between alternate columns) are incredibly useful in certain situations, and if you can learn them you can really gain competitive advantage in the workplace.


However, another part of excel upskilling is learning how to arrange your data so that you only need to do the exploding-head stuff when it's really necessary.


From what you're saying, it sounds like you should rearrange the data in columns (B, D, F...AT) in adjacent columns, and then do the same with columns (C, E, G... AU). Then you'd be able to do a simple SUMPRODUCT on your data, and it will also be easier to maintain.
 
Hi all,


I have a slight variation of the same problem. How different would the formula be if a column (or multiple columns) is added between each set of data? So the requirement is now


A2 = B2*C2+E2*F2+H2*I2+K2*L2+N2*O2+Q2*R2+T2*U2+...+BP2*BQ2


As you can see, column D,G,J,M,P,S...are skipped. (Well, they are used to display other data in the same "project" as the adjacent one to the left.


@Juanito I can't adjust the data columns because each set is pertained to a project. So B2 and C2 is one project while E2 and F2 is another. Others want to see them side by side. I would have rearrange the data set if my pay scale is higher and can make that call. I'm not allowed to do so. You sure will understand the dilemma we face each day. :)
 
=SUMPRODUCT((B2:BP2)*(MOD(COLUMN(B2:BP2),3)=2),(C2:BQ2)*(MOD(COLUMN(C2:BQ2),3)=0))
 
it takes each cell in the range B2:BP2 where MOD(COLUMN(B2:BP2),3)=2

and multiplies them by each cell in the range C2:BQ2 where MOD(COLUMN(C2:BQ2),3)=0


which is Columns B, E, H etc and multiplies them by C, F, I etc

and then adds up the multiples
 
Fred - another possibility is to use SUMPRODUCT. If you put "vol", for example, in row 1 for all columns with a volume value then your formula for total volume could be:

=SUMPRODUCT(B2:BP2,--(B1:BP1="vol"))

It's just another way of making your workbook less error-prone when you add or remove columns and so on.
 
Using juanitos method you would use

=SUMPRODUCT((B2:BQ2)*((B1:BQ1)="a"),(C2:BR2)*((C1:BR1)="b"))
 
Thanks Hui.


Fred - Hui's right (of course!), but you still may find my suggestion of interest if you're really not empowered to rearrange your data in a more logical way!
 
You must log in or register to reply here.
Back
Top