Converting monthly data into quarterly

Originally Posted by paulgerrits

I have tried the suggestion of DonkeyOte and it works great though there seems to be one glitch for the returns given in Column S which gives data from a column too early in the sequence.

Something odd with your file there Paul - if you conduct a Full Recalc (CTRL + ALT + F9) you will find the cells will recalculate generating correct results.

Originally Posted by paulgerrits

how do I re-write the formula if the data would be in columns intead of rows?

If you can assume you always have a full quarter listed (ie 3 months each quarter - irrespective of whether or not values are 0) then using your latest sample:

F13:
=IF(E13="","",IFERROR(LOOKUP(2,1/(D11:D13<>0),D11:D13),0))
copied down

Again, as before the above utilises IFERROR which is not backwards compatible with versions pre XL2007 - revert to:

F13:
=IF(E13="","",LOOKUP(9.99E+307,CHOOSE({1,2},0,LOOKUP(2,1/(D11:D13<>0),D11:D13))))
copied down

if you need a backwards compatible version

of course given this layout you could actually just use an embedded IF if you prefer:

F13:
=IF(E13="","",IF(N(D13)<>0,N(D13),IF(N(D12)<>0,N(D12),N(D11))))
copied down