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"
"Joint 1st of 31"
"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.
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