I searched the web trying to find an answer to find a solution to my excel dilemma, but I could not find an exact solution. I found a few close answers, but not what I need.
I think this might require a macro to do what I am wanting.
I have 3 rows of data (1,2,3). Two rows contain basic entered numbers (1,2). The third row (3) contains a calculation based on the numbers in the same column in the other two rows (e.g. B4=B2+B3, C4=C2+C3, etc.)
I am then using an array formula based on all the numbers in row 2 and the calculated numbers in row 3.
My problem is that I have some blank cells in row 2 causing my array formula not to work for the data I have. I need an automated method of eliminating all the blank cells in row 2 along with the corresponding values in rows 1 and 3 above and below the blank cells. I would like for the columns containing non-blank values to shift left eliminating any colums with a blank value.
The second problem deals with the array formula. I am dealing with a large amount of data and I am not always sure how many or which cells in row 2 may be blank. I want my array formula to use all the nonblank cells in rows 2 and their corresponding values in row 3. This means my array formula will need to change to account for how ever many columns of nonblank data I have available. For example, my array formula may calculate for 10 columns of data, but 4 columns have blank cells causing incorrect results. Therefore, I need all the columns containing a balnk value to be eliminated from the array formula. I assume this could be done by automatically shifting all the columns containing full data to the left and automatically adjusting the array formula for the 6 columns of data rather than 10 columns.
I have attached a sample worksheet. I have simplified my exact formulas into this simplified scenario for better understanding, but the situation is the same.
This array formula entered in B14:F14 will give the required results
=LINEST(N(OFFSET(B12:K12,,SMALL(IF(B11:K11<>"",COLUMN(B11:K11)-COLUMN(B11)),ROW(INDIRECT("1:"&COUNT(B11:K11)))),1,1)),N(OFFSET(B11:K11,,SMALL(IF(B11:K11<>"",COLUMN (B11:K11)-COLUMN(B11)),ROW(INDIRECT("1:"&COUNT(B11:K11)))),1,1))^{1,2,3,4})
That will ignore any columns where row 11 is blank......without actually removing or moving any columns. See attached
Audere est facere
daddylonglegs,
Thank you so much for the help! This looks just like what I need.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks