formula that looks at cell and if it contains text it moves up and looks again

[InvGrp2]

I trade the futures market and I keep tract of my trades.

I'm stumped on trying to figure out how to look at a column of cells and if the contents contains "text" move to the next cell up and look again.

Once it finds a number, displays that number (it could be a negative number or a positive number).

Thanks

[FDibbins]

Try this...
=INDEX(C5:C16,COUNT(C5:C16))

[InvGrp2]

It worked perfectly, thanks.

In trying to understand the formula, could you explain how it looks for numbers vs text?

Thanks.

[63falcondude]

Here's another one:

=LOOKUP(2,1/(C5:C16<>"x"),C5:C16)

The formula from post #2 counts the number of numerical values in C5:C16. In your sample, that count is 5.
The INDEX function then pulls the 5th value from the range C5:C16, which is C9.
Since C9=1, the formula will result in 1.

[FDibbins]

COUNT() only counts numbers while COUNTA() counts everything

This will work AS LONG AS you dont have any other text mixed in the the values above. If that could be the case, 63's suggestion would be better

[InvGrp2]

Both of the formulas worked.

I don't plan on having mixed text but just in case, the 2nd suggestion might be best.

I appreciate both of your suggestions.

Thanks.

Bruce

[FDibbins]

Happy to help

[InvGrp2]

One quick question:

Here's my formula as it applies (its displays the correct #):

=LOOKUP(2,1/(R7:R116<>""),R7:R116)

Here's my formula as it applies if I copy it over the the next column its (its "not" displaying anything):

=LOOKUP(2,1/(S7:S116<>""),S7:S116)

Suggestions?

[63falcondude]

Both of those formulas should return the value of the last cell that is not blank.

Is this what you are looking to do?

If you post a sample sheet showing the issue, we can take a look at it.

[InvGrp2]

I figured it out.

I had " " vs "" in the cell results.

Thanks again.

P.S. I didn't realize "" would leave the cell blank....LOL!

[63falcondude]

Glad you figured it out.

Yes, " " leaves a space and "" leaves the cell blank.

Thanks for the rep!