Solved

Changing array formula so all cell references relative

Posted on 2014-03-05
11
1,423 Views
Last Modified: 2014-03-06
I am using the following array formula in BJ2 using relative references:

=SUM(IF(ISERROR(Y2:BI2)+NOT(ISERROR(FIND((ADDRESS(ROW(Y2:BI2),COLUMN(Y2:BI2))),"AB2,AC2,AD2,BE2,BF2,BG2"))),"",Y2:BI2))

When I copy the formula down the rest of the column, the cell references within the quotation marks are not changing and I need them to:

"AB2,AC2,AD2,BE2,BF2,BG2"

Can the array formula be modified so that all the cell references in the formula are relative, allowing me to copy and paste the formula down the column with references updating as required?

Thanks,
Andrea
0
Comment
Question by:Andreamary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 51

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 total points
ID: 39907030
Hi,

if you only want to change the row

pls try

=SUM(IF(ISERROR(Y2:AE2)+NOT(ISERROR(FIND((ADDRESS(ROW(Y2:AE2),COLUMN(Y2:AE2),4)),SUBSTITUTE("AB2,AC2,AD2,BE2,BF2,BG2","2",ROW(Y2))))),"",Y2:AE2))

Open in new window

Regards
0
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 125 total points
ID: 39907057
try

replacing
"AB2,AC2,AD2,BE2,BF2,BG2"

with addresses formula
"AB"&ROW()&",AC"&ROW()&",AD"&ROW()&",BE"&ROW()&",BF"&ROW()&",BG"&ROW()&""

should handle copying down a column
0
 

Author Comment

by:Andreamary
ID: 39907192
Rgonzo, when trying your solution all the column totals disappeared. Steveo, in trying your solution the columns that were to be excluded from the final total were being included.

Just to summarize the goal of the formula, in case that makes things a little clearer:

Column BJ = Sum of numbers only from Y to BI, excluding AB, AC, AD, BE, BF & BG (the excluded columns have numbers in them but they should not be counted)

Thanks,
Andrae
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:Andreamary
ID: 39907209
Rgonzo, just to add that when using your formula, when I copied and pasted it down several rows the following cell references did not change:

"AB2,AC2,AD2,BE2,BF2,BG2"

So for instance, for row 3 the above references should change to:

"AB3,AC3,AD3,BE3,BF3,BG3"

All the other cell references worked as they should, as they did in the original formula I posted.

Hope that helps...

Thanks,
Andrea
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39907308
Formula is 'deep' w/o building a sheet to debug...

the Sum function is using an If statement? It seems that an a 'false' IF statement in SUM statement will generate the Y2:BI2 address range which would include columns you don't want.

I must ask... if you manually copy / change a couple of rows using your text (manual) method does tallying give you results you expect?

I extracted only the portion of the formula that needed work for relative referencing as your question asked...

I feel both solutions offered should be giving your the relative addressing you are looking for...

I am wondering about formula?  

Please confirm you have verified formula to be giving you what you expect - if manually entered?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 39907519
Hello Andrea,

as written your formula isn't excluding those cells because the ADDRESS function is returning addresses like $AB$2 so those don't match the exclusion list because of the $ signs.

To fix that you need to include a 4 as final argument in the ADDRESS function as per Rgonzo's reply, i.e.

=SUM(IF(ISERROR(Y2:BI2)+NOT(ISERROR(FIND((ADDRESS(ROW(Y2:BI2),COLUMN(Y2:BI2),4)),"AB2,AC2,AD2,BE2,BF2,BG2"))),"",Y2:BI2))

but assuming you have no errors in those cells you can get the same result with simple SUM functions like this:

=SUM(Y2:BI2)-SUM(AB2:AD2)-SUM(BE2:BG2)

that can be copied down so that the references change. If you might have errors in those cell ranges try this version

=SUMIF(Y2:BI2,"<9.99E+307")-SUMIF(AB2:AD2,"<9.99E+307")-SUMIF(BE2:BG2,"<9.99E+307")

regards, barry
0
 

Author Comment

by:Andreamary
ID: 39907686
I've attached a sample spreadsheet with an example of my original working array formula to demontrate what is happening. The formula in the spreadsheet is as follows:

{=SUM(IF(ISERROR(Y2:BI2)+NOT(ISERROR(FIND((ADDRESS(ROW(Y2:BI2),COLUMN(Y2:BI2))),"$AV$2,$AY$2,$BB$2"))),"",Y2:BI2))}

With the above formula, I get the correct answer of "58" in BJ2, but in BJ3, I get the wrong answer of "88" as in that row, as the array formula is not excluding AV3, AY3 or BB3.

I need the following:
The formula to work in all rows in the BJ column, not just in the first row
The excluded columns to be changed from the original "AV, AY, BB" to "AB,AC,AD,BE,BF,BG" (for all rows)

I have tried both solutions offered again, and did not find that they worked for me. Thanks for your patience...hopefully the sample spreadsheet will illustrate the issue more clearly...

Thanks,
Andrea
Array-excludesCells.xlsm
0
 

Author Comment

by:Andreamary
ID: 39907767
Hi Barry,

I saw your comment right after I posted my sample file. I tried the first formula in your post based on Rgonzo's formula, and while it worked for BJ2, when I copied and pasted the formula down the column it did not work for the subsequent rows. Since I might have errors in the cells, I then tried your last formula, and it worked perfectly, both in BJ2 and in the subsequent rows after I copied the formula down the BJ2 column. Thanks very much!!

 BTW, I want to be fair on points distribution here. From my vantage point, the formula that worked for me was the last one supplied, but I'm not an expert and want to make sure I'm not overlooking the usability of the other solutions provided...

Please advise....thanks again!
Andrea
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39907916
Thanks Andrea - the formula in your question won't work as intended, it will sum all the values in Y2:BI2, for the reasons I gave in my answer above.

The first formula I suggested fixes that problem but doesn't fix the dragging down issue, hence my other suggestions.

Objectively I'd say that's the best approach, because of its simplicity, so you should accept my answer!

However I think that the approaches suggested by both Rgonzo1971 and Steveo are also valid. Rgonzo's didn't work for you because the ranges were wrong, all references to Y2:AE2 should be Y2:BI2. Rgonzo also fixed the ADDRESS function problem by adding 4 as 3rd parameter.

Steve's suggestion didn't work because of the flaw in your initial formula - fix that and it works OK too

.......so I would suggest you also award points to both of those experts, the exact split is up to you (and feel free to ignore my advice if you wish!)

regards, barry
0
 
LVL 8

Expert Comment

by:5teveo
ID: 39907932
Many aspects of this question and it's final resolution...

I agree w/ Barry splitting (somehow) seems fairest...

bottom line... is you should have information to resolve your initial problems.

Congrats and happy coding...
0
 

Author Closing Comment

by:Andreamary
ID: 39910812
Thanks, Barry, for your explanation on the problem with my original formula and the impact of that on potential solutions, as well as the guidance on awarding points, and last but not least, your elegant solution! And thanks, Rgonzo, for your generous note and solution, and to Steveo for your solution too.

I'm very pleased...

Andrea
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question