Hey,
I am trying to solve the following problem:
I am using the formula below, to calculate the diagonal sum:
{=SUM(N(INDIRECT(ADDRESS(ROW(32:34);ROW(2:4)))))}
However, I want to replace the second cell reference of the ROW definition, i.e. here 34 and 4 with a variable, or value of a cell.
How can I do this?
Thanks a lot in advance!
Hi,
That's not a very rigorous construction in any case. Can you please post a workbook with an example and your expected result?
Regards
Example.PNG
I hope that the screenshot examplifies the problem well enough.
I want to avoid, having to change the sum formula each time, I change the number of cells to be added up.
Sorry - I meant an actual Excel workbook.
Regards
You mean uploading an excel file? I am unable to find this sort of button. I am sorry, but I am new to this forum...
No worries. Click on Go Advanced and scroll down to Manage Attachments.
Regards
EXCEL FORUM.xlsx
The diagonal sum, which I am struggeling with is in row 91. I want to make the number of cells to be added up dependent on L1.
Thanks.
It's quite a nice approach that you have there, and certainly quite succinct, though also volatile, inflexible, unrigorous and requiring array-entry.
I personally would prefer a slightly longer formula, though which has none of those drawbacks. In B91:
=SUMPRODUCT((ROW(B47:AP87)-MIN(ROW(B47:AP87))+1=COLUMN(B47:AP87)-MIN(COLUMN(B47:AP87))+1)*(COLUMN(B47:AP87)-MIN(COLUMN(B47:AP87))+1<=$L$1)*B47:AP87)
though this is a single-cell solution, and I'm not quite sure what your intended results are, i.e. whether you want this formula to be dragged across to the right and, if so, how this should be reflected in the results.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks