In cell AR12 I have entered the following formula, which works fine. It is
designed to show the last figure entered in any 4th cell in the range G12 to
AN12 (inclusive). For example, if the last figure entered, appears in cell
V12, then AR12 should show the V12 value.

=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH 12,AK12,AN12)=0,"",INDIRECT(CONCATENATE("R",ROW(), "C",((COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12, AH12,AK12,AN12))*3)+4),FALSE))

However, I would now also like to show the second to last figure entered, in
order to do a comparison. So where V12 contains the last available figure, I
would like to show in AS12, the value of S12 (the 4th cell prior to the last
entered figure).

Any ideas?

=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH 12,AK12,AN12)=0,"",INDIRECT(CONCATENATE("R",ROW(), "C",((COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12, AH12,AK12,AN12))*3)+1),FALSE))

"Sarah (OGI)" wrote:

In cell AR12 I have entered the following formula, which works fine. It is
designed to show the last figure entered in any 4th cell in the range G12 to
AN12 (inclusive). For example, if the last figure entered, appears in cell
V12, then AR12 should show the V12 value.

=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH 12,AK12,AN12)=0,"",INDIRECT(CONCATENATE("R",ROW(), "C",((COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12, AH12,AK12,AN12))*3)+4),FALSE))

However, I would now also like to show the second to last figure entered, in
order to do a comparison. So where V12 contains the last available figure, I
would like to show in AS12, the value of S12 (the 4th cell prior to the last
entered figure).

Any ideas?

Great, that worked!
Cheers

"Toppers" wrote:

=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH 12,AK12,AN12)=0,"",INDIRECT(CONCATENATE("R",ROW(), "C",((COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12, AH12,AK12,AN12))*3)+1),FALSE))

"Sarah (OGI)" wrote:

In cell AR12 I have entered the following formula, which works fine. It is
designed to show the last figure entered in any 4th cell in the range G12 to
AN12 (inclusive). For example, if the last figure entered, appears in cell
V12, then AR12 should show the V12 value.

=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH 12,AK12,AN12)=0,"",INDIRECT(CONCATENATE("R",ROW(), "C",((COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12, AH12,AK12,AN12))*3)+4),FALSE))

However, I would now also like to show the second to last figure entered, in
order to do a comparison. So where V12 contains the last available figure, I
would like to show in AS12, the value of S12 (the 4th cell prior to the last
entered figure).

Any ideas?

"Toppers" wrote...
=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,A H12,AK12,AN12)=0,"",
INDIRECT(CONCATENATE("R",ROW(),"C",((COUNT(G12,J1 2,M12,P12,S12,V12,Y12,
AB12,AE12,AH12,AK12,AN12))*3)+1),FALSE))
....

Doesn't have to be volatile. Since this would be entered in AS12,

=IF(COUNT((G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,A H12,AK12,AN12))<2,"",
INDEX((G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH12, AK12,AN12),1,1,
COUNT((G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH12, AK12,AN12))-1))

or

=IF(COUNT((G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,A H12,AK12,AN12))<2,"",
INDEX(B12:AN12,COUNT((G12,J12,M12,P12,S12,V12,Y12, AB12,AE12,AH12,AK12,
AN12))*3))

or

=IF(SUMPRODUCT((MOD(COLUMN(G12:AN12),3)=1)*ISNUMBE R(G12:AN12))<2,"",
LOOKUP(2,1/(MOD(COLUMN(G12:AN12),3)=1)/ISNUMBER(G12:AN12),D12:AK12))

Note: I changed the condition from =0 to <2 because if there's only one
value in these cells (in G12), there's no previous value.