Excel 2007 : Formula returning last value needs few tweaks

Hi guys

We are using a formula which returns the last value.
In the uploaded file you'll see that it doesn't deliver the wishful return in 2 occasions.
In the first occasion it's understandable because we don't know how to give the right command to ignore zeros. Here is where we need the first tweak.
In the second occasion we don't have a clue, why it doesn't returns anything. Here probably we need more than 1 tweak, perhaps another approach.

We would greatly appriciate any help

@mcmay

This worksheet has been made by me.
I'm the one who applied those formattics.
So I know what the worksheet contains.
What I'm seeking, is help to overcome the bellow difficulties
The first question is : how to modify the formula in order to ignore the zeros contained in ANY column (not only O&Pwhich are a very nice example)
The second question is : what should I do to have the last value from column R (provided that the existing formula can't be modified for whatever reason in this column)

The uploaded worksheet is a small part of the actual.
Those references don't matter, because they don't affect the outcome.
I've checked.

To understand what I'm saying please do this : erase the contained formulas from those cells (A24:B26)

Originally Posted by Md Aejaz

Dear,

check it
is this what u are looking for

Attachment 162170

Nope! it's not

Once more
Modification of the existing formula is what I'm looking for.
The worksheet and its content has nothing to do with formula's failure in these specific columns

Are you sure ?

I check. I didn't find anything except the -1 entered by you in O column formula.
Can you show me please where are these "mistakes" ??????????/

Σπύρο(Spiros)

In O27

=LOOKUP(2;1/(O3:O26>0);O3:O26)

In P27

=LOOKUP(2;1/(P3:P26>0);P3:P26)

And i don't know, if you can live without Concatenate in R column. This is why your formula, does not work.
In R3 and copy, until R26

=IFERROR(SUM(J3/L3);"")

Then your formula In R27, works.

For the first 2 formulas
Thanks a lot my friend.

BRAVO Fotis !
Neat and efficient solution.

About the third formula we have a significant issue
In R column the format has to be kept because is the format of ratio.

Can you do something about it ?

Maybe we can use the "back" door my friend.

What i mean is this.

In X3 and copy down to X26, use this.

=IFERROR(ROUND(J3/L3;1);"")

In X27, this.

=IFERROR(INDEX(X3:X26;MATCH(10^99;X3:X26));"")

Hide this helper column.

Now, in R27, put this.

=X27&":1"

Your basic formulae are inconsistant with what you are trying to do.

Have a look at the changes I have made in Columns Q:T, you can change the formulae to IFERROR(), I only have access to 2003 at present.

1/. Use 10^99 to find last number, and Rept("z",255) to find the last text entry.
2/. When errors are returned in numeric columns default to "" (Null String)
3/. When errors are returned in text columns default to 0 (Number zero), format the columns to suppress zeros if you wish.

@ Marcol

I tried with "and Rept("z",255)", but i did not the correct format in R column.

The details make the difference in our level...

Nice job Marcol!

@ Fotis1991
Did you change the formula in R3 to

Please Login or Register  to view this content.

or for 2007 and above ...

Please Login or Register  to view this content.

Drag/Fill Down to R26, then format the range R2:R26
Number format > Custom
Type:=

Please Login or Register  to view this content.

Then R27 becomes

Please Login or Register  to view this content.

or for 2007 and above ...

Please Login or Register  to view this content.

What i mean is,that i tried to solve the problem using this function(before my post 12 and your post 13) and i could not make this to work, because i did not even thouqht to change the format in R column....

Your solution is great and (for me), works like a charm!

About Fotis I already said what I had to say.

About Marcol ....

Simply....