[SOLVED] Replace randomly occuring and grouped zeros with last non-zero number from the column above

Replace randomly occuring and grouped zeros with last non-zero number from the column above

I'm trying to ultimately generate a cell containing text saying what the ranking of a data element from a data set is.

e.g. I'm trying to achieve :

"Joint 1st of 31"

"4th of 31"

etc.

Generating the first number of that string is the only problem I have.

Cells in right hand column AG are to check if a zero is encountered to the left in column AF.

If a zero is found then read UP column AF and grab the last non-zero number and write it out at the appropriate place in AG. So in column AG I should have 1 1 1 4 5 6 6 8 8 8 ...

I really need a formula like =if(AF{row}=0,<AF{upwards from {row} until non-zero found>)

Is there a named function to achieve this? Tq. in advance for any input.

Solution

I have it sussed! In AG8 enter the following formula....

=IF(AF8=0,AG7,AF8)

Took best part of a day. Seems so easy now but only became apparent after many, many attempts. Seems rather 'hacky' too but hey, if it works... :D