Formula needed to show the second to last figure in range of cells

S

Sarah (OGI)

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,AH12,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?
 
T

Toppers

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

Sarah (OGI)

Great, that worked!
Cheers

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

Harlan Grove

Toppers said:
=IF(COUNT(G12,J12,M12,P12,S12,V12,Y12,AB12,AE12,AH12,AK12,AN12)=0,"",
INDIRECT(CONCATENATE("R",ROW(),"C",((COUNT(G12,J12,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,AH12,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,AH12,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)*ISNUMBER(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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top