Adding to a working Array formula gives error

Hi,

I have a spreadsheet with a column of data that has frequent gaps.  I want to calculate the difference between each of the values in that column and the previous non-blank value in the column; the result will go into a separate column adjacent to the values.

Column E (Weight) is my Data Values and Column F (Change) is where I want to put the difference.

The basic Table
The Weight column can be entered on any row as the table grows and I need to give this to a user that just enters the weight value and nothing else.  They are not Excel users so I don't want them copying formula etc.

If I put this formula into the cells in Column F (Change) I get the difference correctly calculated between the adjacent value and the previous value from Column E (Weight)

=E10-OFFSET($E$3, MAX(IF(NOT(ISBLANK($E$3:E9)), ROW($E$3:E9),0))-ROW($E$3),0)

The issue is that all the blank cells show unnecessary values and so I want to make them invisible.

The table with the working array formula
To fix that I added an IF(ISBLANK statement to the formula to make it

=IF(ISBLANK(E10),"",E10-OFFSET($E$3, MAX(IF(NOT(ISBLANK($E$3:E9)), ROW($E$3:E9),0))-ROW($E$3),0))

only now I get a #VALUE error.

The table showing the error
I am stuck.  Can anybody please help?

thanks
Andrew
Andrew HalfordAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
It works for me. However, I did have to delete the space before the word "MAX" before it would work.
Andrew HalfordAuthor Commented:
Thanks for the quick reply Philip,  I checked and I had already removed the space from my formula after I posted the question.  I even tried creating a new spreadsheet and testing it but I still get the same #VALUE error.

I even tried it on a different machine with Excel 2010 and got the same results.  

Any ideas what can I be doing different?

Like I said, the initial command works until I add the extra IF statement and then I get the error.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please find it attached working. Try copying it and pasting it from there.
EE150327.xlsx
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
Is there possibility that the weight entered could be higher than previous weight entered, ie the difference would be positive rather than negative?

With current formula, also working for me in Philips's workbook, I have copied down a few more rows and then entered a higher weight of 276.5 and the result says -6.1 ie the 276.5 less the original 282.6
Andrew HalfordAuthor Commented:
Thanks guys,

@Rob, yes there is a chance of the weight going back up (it is supposed to be a weight loss program but we all know how that works sometimes right :))

@ Phillip, it is strange, I opened your sheet and saw the correct values but when I hit F2 on the cell and the then hit CTRL+SHIFT+ENTER the #VALUE error came back.  Should I not be using the CTRL+SHIFT+ENTER to enter the formula?  I thought that was required with Arrays?

Building on Rob's comment, after I edited your sheet and then just hit Enter to put the formula back in I started getting the Changes all compared to the initial value and not the incremental differences.

Yet, if I just do the initial formula

=E10-OFFSET($E$3,MAX(IF(NOT(ISBLANK($E$3:E9)),ROW($E$3:E9),0))-ROW($E$3),0)

then everything calculates correctly.

FYI:  I have added a hidden column to do this calculation and then in the next column (G) I do the

=IF(ISBLANK(E10),"",F10)

which gives me the result.  It just puzzles me why I cannot combine these two successfully.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You don't need Ctrl+Shift+Enter for your formula.
Andrew HalfordAuthor Commented:
I found that if I don't do the Ctrl+Shift+Enter I just get the difference from the initial value in cell E3, or the overall weight loss.  

If I use the Ctrl+Shift+Enter I actually get the difference from the previous value in the table.
Saurabh Singh TeotiaCommented:
Andrew..

Apply this formula in F4 and drag this..it will do what you are looking for...

=IF(E4="","",E4-LOOKUP(2,1/(E$3:E3<>""),E$3:E3))

Your workbook...

Saurabh...
EE150327.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
Hello Andrew,

For a good working solution I'd go with Saurabh's suggestion, but it's interesting why you get those results with your formula - I know part of the reason but perhaps not the whole story.

Firstly if you use this formula:

=IF(ISBLANK(E10),"",E10-OFFSET($E$3, MAX(IF(NOT(ISBLANK($E$3:E9)), ROW($E$3:E9),0))-ROW($E$3),0))

It needs to be "array entered" - this part demands it:

IF(NOT(ISBLANK($E$3:E9)), ROW($E$3:E9),0)

If you don't use CSE then that part will just return the value for the first row, hence why you get the difference from the initial value rather than the last one.

You get #VALUE! because ROW function returns an array like {3} rather than a value - even for a single row, and in some circumstances Excel has problems processing that array.

In this case the first ROW function is OK but the ROW($E$3) part at the end is causing the problem - you can fix it by wrapping that function in another function like MAX or SUM which won't change the value but will change the array to a value.

For example this works

=IF(ISBLANK(E10),"",E10-OFFSET($E$3, MAX(IF(NOT(ISBLANK($E$3:E9)), ROW($E$3:E9),0))-SUM(ROW($E$3)),0))

confirmed with CTRL+SHIFT+ENTER

What I don't understand is why that ROW function works OK in the initial formula but then fails when you add your IF function - when I go through formula evaluation that row function initially resolves to just 3 in the original formula but somehow becomes {3} for the same formula but inside an IF function.

regards, barry
Andrew HalfordAuthor Commented:
Saurabh,

That works perfectly :)

Thank you!!
Andrew HalfordAuthor Commented:
@Barry, thanks for your comments, at least that identifies what is failing even if we cannot work out why!

My OCD will undoubtedly keep me looking at this and so I will post here if I find out anything.  Meanwhile, maybe somebody will come across this thread and can offer some more insight.

Thanks to everybody for the quick responses today.  It is really appreciated.

Andrew
Rob HensonFinance AnalystCommented:
Just thought of logic:

If blank then blank else Original Weight less Current weight less sum of previous differences

No need for arrays

Does that work?

EDIT (at sensible hour rather than middle of the night):

Formula for F4, copied down:
=IF(E4="","",E4-$E$3-SUM(F$3:F3))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.