0

I'm really new to array formulas. The flow should be IF the list has NO data entered in the array or only 1 entry, it shows "Add Weight" [7 total spots). Once two pieces of any data has been input anywhere in the array, it checks for the FIRST value in the array and subtracts the LAST value in the array.

This issue I'm getting is the cell shows #value when the array is empty instead of showing "Add Weight". The formula works perfectly once data is input and correctly subtracts the last entry from first entry regardless of their placement within the array.

This looks up the last entry in the array.

LOOKUP(2,1/(1-ISBLANK(D13:D19)),D13:D19))

This looks up the first entry in the array.

INDEX(D13:D19,(MATCH(FALSE,ISBLANK(D13:D19),0)))

Is it because the formula is marked as an array?

=IF(COUNTIF(DailyProgress[Change],"")>6,"Add Weight",LOOKUP(2,1/(1-ISBLANK(D13:D19)),D13:D19))-INDEX(D13:D19,(MATCH(FALSE,ISBLANK(D13:D19),0)))

If there is a better way to write this, I'm all ears.

EDIT:

In an adjacent column I have it showing the change from the previous cell like so:

=IF([@[Weight (lbs)]]="","",[@[Weight (lbs)]]-D17)

Where D17 is the cell above the current row [This would be entered on row 18].

As it is, if the cell right above the adjacent cell is empty it shows it as zero and does "0 - value= -value" so with the change we just did I want it to compare it to the LAST input cell before it instead of the cell directly above it. I know this will need to be rewritten completely. Still using the D13:D19 array.

Improve this question
4
  • 1. ISBLANK will return false if the cell has a formula even if that formula is returning "". 2. Did you enter it with Ctrl-Shift-Enter instead of Enter when exiting edit mode. Commented Feb 28, 2017 at 18:57
  • The list for the array are all empty and without formulas. I also am doing CTRL+SHIFT+ENTER Commented Feb 28, 2017 at 18:58
  • maybe post some test data and expected outcome. Commented Feb 28, 2017 at 18:58
  • @ScottCraner Just added what I'm experiencing, I realized I missed the error I'm receiving Commented Feb 28, 2017 at 18:59

1 Answer 1

Reset to default
1

Use this array formula:

=IFERROR(IF(MATCH(FALSE,D13:D19="",0)=MATCH(1E+99,D13:D19),"Add Weight",INDEX(D13:D19,MATCH(1E+99,D13:D19))-INDEX(D13:D19,MATCH(FALSE,D13:D19="",0))),"Add Weight")

Until there are at least two entries in D13:D19 it will Return Add Weight

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

FWIW:

The reason your COUNTIF did not work is the criteria, Change the "" to "=" and it would count the blanks:

COUNTIF(DailyProgress[Change],"=")>6
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

You're a legend. This worked perfectly. So I can understand this a little more, we use the 1E+99 because we're dealing with integers? How does this value get determined? Sorry for the annoying questions.
@BetaOp9 yes with numbers 1E+99 if it were text then it would be "ZZZ" to get the last cell. Also just realized why your COUNTIF did not work. I will add that to the answer.
That explains why the countif wasn't working. So with this change comes another question I put into the original question. Any insight?
That is a new question that deserves a new post.
Fair enough! I have to wait 90 minutes to make a new post it seems.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.