Hello. I am using this formula to sum values in a row. However, when I insert a column to the left of A, the formula no longer calculates correctly. Could someone please breakdown this formula and explain why inserting a column to the left of A would ruin the formula. Thanks.
=SUMPRODUCT(1-COLUMN($A3:A3)*8.3333%,N(OFFSET($A2,,COLUMNS($A3:A3)-COLUMN($A3:A3))))
It's the use of the COLUMNS() function. That's an "incrementing" trick people use to get a formula to increment up in value from 1 to 2 to 3, etc, as you copy a cell to the right. As you copy to the right the range of cells within the COLUMN() reference will expand causing the resulting numeric value to increase by one as it goes across columns.
So, if you're changing the layout of your workbook and inserting a new column A, you will ahve to go back and manually "fix" all those formulas again to set their ranges back to $A$3:A3, or whatever they are to start with.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks