tag:blogger.com,1999:blog-7958828565254404797.post1507957134449932087..comments2020-01-27T01:02:36.674-08:00Comments on ListenData: Excel : Sum diagonal cells in a rangeDeepanshu Bhallahttp://www.blogger.com/profile/09802839558125192674noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-7958828565254404797.post-76276202301952786462019-12-05T07:14:46.492-08:002019-12-05T07:14:46.492-08:00Just wanted to say THANK YOU! This formula was exa...Just wanted to say THANK YOU! This formula was exactly what I was looking for, and it's an elegant solution that does require array formulas (which most people who are using the spreadsheet I created don't understand). Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-12347838343288097172019-01-25T07:04:30.424-08:002019-01-25T07:04:30.424-08:00Hi, Is there any way to use this dynamically withi...Hi, Is there any way to use this dynamically within a table? I have a table which expands weekly and need to sum the diagonals. I am manually updating some very long equations but it would be far simpler and more practical to have it expand automatically as the table does. Any direction in this would be really appreciatedMark Tetleyhttps://www.blogger.com/profile/00674986080888102569noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-88809502077201010822016-10-03T14:40:29.352-07:002016-10-03T14:40:29.352-07:00My pleasure. Glad you could use it.
Regards
Skou...My pleasure. Glad you could use it.<br /><br />Regards<br />SkouperdAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-61754425661743140072016-08-27T04:54:52.062-07:002016-08-27T04:54:52.062-07:00I have added the explanation. Thanks!I have added the explanation. Thanks!Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-80530722913789739492016-08-27T04:54:18.154-07:002016-08-27T04:54:18.154-07:00Thank you for your appreciation!Thank you for your appreciation!Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-56094311757956149702016-08-27T04:53:58.290-07:002016-08-27T04:53:58.290-07:00Thank you for your feedback. I have added the desc...Thank you for your feedback. I have added the description. Cheers!Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-39583623242724457902016-08-27T04:53:14.594-07:002016-08-27T04:53:14.594-07:00Awesome! Thank you for taking the time to explain ...Awesome! Thank you for taking the time to explain the formula in detail. I have added the explanation to the article.Deepanshu Bhallahttps://www.blogger.com/profile/09802839558125192674noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-14629828699401929542016-08-25T16:33:21.329-07:002016-08-25T16:33:21.329-07:00Great formula, thanks a lot. Since you saved ne a...Great formula, thanks a lot. Since you saved ne a lot of processing power with sharing this formula, I will now take the time to explain how it works to your subscribers whom queries it. This is a nice and elegant solution, well done.<br /><br />I am breaking the formula down and explain what each step in it does:<br />>> ROW($B$3:$G$8)<br />returns the values 3,4,5,6,7,8. This represents the row number of the table you are summing up (if the table started at say B2, then the first number would be a 2). If you enter that part of the formula into an array (select 5 rows, type in the formula and then press CTRL + SHIFT + ENTER) then you will see that result<br /><br />>> -MIN(ROW(B3:G8))<br />returns the smallest row number from the array above, being a 3 in this scenario. Again, if you want to see the output just enter the formula into an array.<br /><br />>>(ROW($B$3:$G$8)-MIN(ROW(B3:G8)) <br />if we combine the above two parts of the equation, then we basically get a "matrix" that reads, 0,1,2,3,4,5 which is effectively the row number of the cell you are trying to sum.<br />(3 - 3 = 0, 4 - 3 = 1, 5 - 3 = 2 etc)<br /><br />>>=MIN(COLUMN(B3:G8))<br />This returns 2 for all the values (because the table starts at column B). You can test it by selecting 6 columns (one row) enter the formula and then press (CTRL+SHIFT+ENTER)<br /><br />>>-COLUMN($B$3:$G$8))<br />Basically returns the column number, being 2,3,4,5,6,7 (starting at 2 because the first column is B). You can validate it by selecting 6 columns (one row) and pressing CTRL+SHIFT+ENTER<br /><br />>> MIN(COLUMN(B3:G8))-COLUMN($B$3:$G$8))<br />combining the above two basically indicate a counter for the column you are working in, being 0,-1,-2,-3,-4,-5<br /><br /><br />>>(ROW($B$3:$G$8)-MIN(ROW(B3:G8))=MIN(COLUMN(B3:G8))-COLUMN($B$3:$G$8))<br /><br />the above function now compares the two matrixes with each other, the "row" part looking like this:<br />0 0 0 0 0 0<br />1 1 1 1 1 1<br />2 2 2 2 2 2<br />3 3 3 3 3 3<br />4 4 4 4 4 4<br />5 5 5 5 5 5<br /><br />while the Column part looks like this:<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br /><br />The only two cells that have a matching value is the top left cell, as per the below boolean table:<br /><br />TRUE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br /><br />>> *($B$3:$G$8)<br />this means that it will only sumproduct the parts of the matrix that is TRUE, being the top left cell.<br /><br />hence the result for the first cell.<br /><br />If you look carefully at the formula, you will notice that the min parts of the columns and rows is not fixed, as such, as you move the formula down one row, the matrices looks like this:<br /><br />ROWS: -1 -1 -1 -1 -1 -1<br />0 0 0 0 0 0<br />1 1 1 1 1 1<br />2 2 2 2 2 2<br />3 3 3 3 3 3<br />4 4 4 4 4 4<br /><br />COLS<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />0 -1 -2 -3 -4 -5<br />since we only copied it one row down and did not shift the columns the result looks the same<br /><br />and the boolean matrix looks like this:<br />FALSE TRUE FALSE FALSE FALSE FALSE<br />TRUE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br />FALSE FALSE FALSE FALSE FALSE FALSE<br /><br />and so forth.<br /><br />as mentioned, great formula, well written.<br />Regards<br />Skouperd<br />(www.skoups.com)<br /><br /><br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-45173317363376503632016-04-19T03:30:25.274-07:002016-04-19T03:30:25.274-07:00I agree with comment on 10 Dec 2014. That would al...I agree with comment on 10 Dec 2014. That would also help with tweaking the formula to suit own situation (size of triangle and what it is that you are trying to sum).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-56889101482486971802015-12-31T06:26:05.695-08:002015-12-31T06:26:05.695-08:00DUDE!!!! AWESOME. saved me about 2 weeks of copy...DUDE!!!! AWESOME. saved me about 2 weeks of copying and pasting data diagonally. Unknownhttps://www.blogger.com/profile/11706690445462823366noreply@blogger.comtag:blogger.com,1999:blog-7958828565254404797.post-17966170999927391522014-12-10T09:19:21.217-08:002014-12-10T09:19:21.217-08:00Hi, it is a great site, I would like to suggest th...Hi, it is a great site, I would like to suggest that you please explain your formulas as how it works and the logic behind, so it will be more useful than just copying as it is without understanding it. Thanks!razaashttps://www.blogger.com/profile/06100879630950646046noreply@blogger.com