Solved Why does the following function return #VALUE!

December 4, 2014 at 17:59:57
Specs: Windows 7
Cells (A,B),(C,D),(E,F) & (G,H) ARE MERGED TO FORM 4 CELLS. This Function is designed to give me the result of the last cell in the row that contains value greater than 0. =IF(COUNT((A1:B1):(G1:H1))=0,"",OFFSET((A1:B1),0,COUNT((A1:B1):(G1:H1))-1)). It works ok to the end but then does not recognize merged cells instead counts cells individually as if not merged. Can you help

See More: Why does the following function return #VALUE!

Report •

#1
December 4, 2014 at 20:15:12
✔ Best Answer
First I'll answer the question asked in your subject line, then I'll offer a different method to get the result you want.

If you use the Formula Evaluator on your formula, you will eventually get to a point where you will see that the OFFSET((A1:B1),0,COUNT((A1:B1):(G1:H1))-1) evaluates to a range, not a single cell. e.g. $A$1:$B$1.

You can't return value from a range of cells into a single cell. If you were to enter =$A$1:$B$1 in a cell, you would get the same #VALUE error.

As for your ultimate goal, the key thing to remember is that when you merge cells, the value is actually only in one of the cells of the merged range. You can prove this to yourself by putting a value in a merged cell and then testing each individual cell in the merged range to see where the value actually exists. Depending on how and when you merged the cells, the value is usually in the upper left cell, but that's not true in all cases.

In your situation, the values are probably in A1, C1, E1 & G1, therefore we don't have to worry about using "merged cell references" in the formula.

The following formula can be used to find the last value in a horizontal range of cells. Since you only have 4 cells with values in A1:H1, with the rest of the cells being empty, it should return the results you are looking for:

=IF(MAX(A1:H1)=0,"",OFFSET(A1,0,MATCH(MAX(A1:H1)+1,(A1:H1),1)-1))

Note: The first MAX function could be replaced with your COUNT function or any one of a number of functions that checks to see if a value exists in that range. I used MAX simply because it was "consistent" with the MAX function used later in the formula.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 4, 2014 at 20:42:44
Works perfectly, thanks for your help

Report •

#3
December 5, 2014 at 20:21:36
FOLLOWING UP ON THE SITUATION YOU RESOLVED FOR ME, IS IT POSSIBLE TO ALSO BE ABLE TO RECORD THE SECOND LAST RESULT.

Report •

Related Solutions

#4
December 5, 2014 at 21:55:51
Please do not post in all upper case. All upper case is the internet equivalent of yelling and nobody likes to be yelled at.

I'll work on a solution and get back to you if I come up with something.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
December 6, 2014 at 05:38:38
My apologies, oversight on my part

Report •

#6
December 7, 2014 at 18:55:14
OK, I think this work for all cases. It's a brute force method, but I couldn't come up with anything more elegant. There may be a way to shorten it, but I'll leave that to you.

Note: If there is only 1 value in your range, this formula will return an empty cell because there is no "second to last" value.

I've split the formula onto multiple lines just to make it easier to see what it does. Basically, it counts how many values are in the range and then does a bunch of tests based on what possibilities exist for that count.

=IF(COUNTA(A1,C1,E1,G1)=4,E1,

IF(COUNTA(A1,C1,E1,G1)=3,
IF(G1="",C1,IF(E1<>"",E1,C1)),

IF(COUNTA(A1,C1,E1,G1)=2,
IF(G1="",IF(E1<>"",IF(C1<>"",C1,A1),A1),
IF(E1<>"",E1,IF(C1<>"",C1,A1))),"")))

========================================

Explanation:

=IF(COUNTA(A1,C1,E1,G1)=4,E1,
If there are 4 values, E1 must be second to last

IF(COUNTA(A1,C1,E1,G1)=3,
IF(G1="",C1,IF(E1<>"",E1,C1)),
If there are 3 values and G1 is empty, E1 must be the last value, making C1 second to last. If G1 is not empty, the second to last could be E1, unless E1 is empty, then it must be C1.

IF(COUNTA(A1,C1,E1,G1)=2,
IF(G1="",IF(E1<>"",IF(C1<>"",C1,A1),A1),
IF(E1<>"",E1,IF(C1<>"",C1,A1))),"")))
If there are only 2 values, we have to do a lot more checking. I'll leave it to you to go through this last bit of logic. The final argument ("") is the value_if_false for the entire formula, providing the empty cell for when there is only one value in the range.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question