Vlookup

[matthewlouis]

Column R18 uses the formula =IFERROR(IF(D18<$H$3,"Failed",""),"")

D = current price

$H$3 is the price I am comparing D18 to. $H$3 is constant, of course

Once the price in Column D becomes LESS THAN the fixed price of $H$3, the cell in R18 says "FAILED." That's great. But when I copy the formula down and the cells in Column D become > than $H$3 again, Column D's price shows up again . . . I don't want that. Once a cell in Column R says "FAILED, I want every Column R cell AFTER that to say "FAILED" regardless if any of the following prices in Column D show up > $H$3 somewhere down the column.

This is messing up my Lookup formula in R4 =IFERROR(VLOOKUP("Failed",$R$6:$R$133,1,FALSE),"") because it's looking up everything AFTER R18 and including it in the Lookup.

Any ideas? Thanks in advance!!

 

[matthewlouis]

Joe, the cell is blank but, yes, there is a formula in there where, if the date in Column A is today, it returns the price in B20. AA19 is blank and it should be blank because B19 (235.77) is not greater than $G$3 (266.07).

Since B20 is blank (that's tomorrow's price, it hasn't printed yet, it is an external formula), there is a blank in B20, hence the result in AA20 should be blank . . . but it's showing Failed.

When I do LEN(B20) it returns a 6 (??). LEN(AA19) returns 0.

So you're saying even though there's a blank in B20, there's something there? In my previous posts on this thread, the blanks returned a blank, not Failed like it is on this sheet.

 

[matthewlouis]

Joe, did I not give all the needed information this time?

 

[Joe4]

If LEN(B20) returns 8, it is telling you that it is NOT blank, but rather there is a string that is 6 characters long in that cell (it could be something like 6 spaces).
Note that spaces are NOT the same as "blank". Spaces ARE something.

What is the formula in cell B20?
Is there any custom formatting or conditional formatting being applied to that cell?

 

[matthewlouis]

If LEN(B20) returns 8, it is telling you that it is NOT blank, but rather there is a string that is 6 characters long in that cell (it could be something like 6 spaces).
Note that spaces are NOT the same as "blank". Spaces ARE something.

What is the formula in cell B20?
Is there any custom formatting or conditional formatting being applied to that cell?

OK, I see. Yeah LEN(B20) returned 6, not 8.

The formula is cell B20 is =IF(A20=$H$2,xlqHigh($A$1,"Yahoo"),xlqhHigh($A$1,A20,"Yahoo"))

where:

A20 is tomorrow's date (8/13/2019)

$H$2 = today

$A$1 = ^gspc, the symbol for the S&P 500 Index

This is a program called XLQ that goes out and gets all stock price information from Yahoo.

Of course, it returns a blank because tomorrow isn't here yet . . . that's why I though it would return a blank, not Failed. But as you said, the cell must not be blank.
What do you think?

 

[crystalneedshelpplzthnx]

Hey Crystal,

Stumped again. The formula you gave me above works great. However, I am on another spreadsheet and I need to use the same formula except I need to us > and not <.

When I enter =IF(AA19="Failed","Failed",IF(B20>$G$3,"Failed","")) I get Failed when B20 is blank. On the other sheet you helped me, this was not a problem. That formula was =IFERROR(IF(R9="Failed","Failed",IF(C22<$G$3,"Failed","")),"") would return a blank, the only difference in these is the column and I am using > and not <

I have no idea. Same formula . . . using > (doesn't work) instead of < (works).

I am stumped! any idea?

Thanks in advance!

This should work for both spreadsheets

=IF(B20="","",IF(OR(AA19="Failed",B20<$G$3),"Failed",""))

Crystal

 

[matthewlouis]

Hi Crystal, no, that also returned Failed, not a blank. And the last part is B20>$G$3, not less than . . . this is a reversal of the formula you solved for me last week. In other words, if the price in B20 is > the price $G$3, the pattern fails.

As Joe said in previous post, B20 is not blank even though it's blank in the cell because Len(B20) returns 6

 

[crystalneedshelpplzthnx]

I see, could you upload the spreadsheet to a share site so I could potentially narrow down the issue?

Crystal

 

[matthewlouis]

XLQ has formulas that when shared, they don't work so that would defeat the purpose. It would just give errors. Hmmm, what else could I do to help you?

What I don't understand is it worked perfect what you suggested on the other spreadsheet. This one is just a reversal of that one (bullish vs. bearish)

 

[crystalneedshelpplzthnx]

Well, what I was initially thinking was, the result was being treated as a zero. But when you and Joe determined something other than a blank is there, I think a "work-around" formula should work, only problem is...what are we working around? Sorry I couldn't help further.

 

[Joe4]

The formula is cell B20 is =IF(A20=$H$2,xlqHigh($A$1,"Yahoo"),xlqhHigh($A$1,A20,"Yahoo"))

What is xlqHigh?

Is it some user-defined function?
If so, what is the VBA code behind it?

That function is where your problem lies. It is NOT returning null/blank values, but rather a series of characters, whether they are spaces, or some blank invisible characters (like non-breaking spaces).

Let's try to figure out what is in that cell.

Paste this User Defined Function into a VBA module:

Function ReturnASCII(myRange As Range) As String

    Dim ln As Long, i As Long
    Dim temp As String
    
'   Get length of string
    ln = Len(myRange)
    If ln = 0 Then Exit Function
    
'   Loop through all characters
    For i = 1 To ln
        temp = temp & Asc(Mid(myRange, i, 1)) & "-"
    Next i
    
'   Return ascii values
    ReturnASCII = Left(temp, Len(temp) - 1)
    
End Function

Then place this formula in any blank cell and tell me what it returns:

=ReturnASCII(B20)