Problem showing quarterly figures

**welshman1982** · 12-15-2009, 09:51 AM

Hi, I have the following problem. Probably very simple to solve but I'm at a loss.

I have a table showing quarterly returns (cells A1-A4 labeled Q1 etc, cells B1-B4 with values) with a current total at the bottom (B5).

We have returns in for Q1 and Q2 so these cells have values in them, but nothing in for Q3 or Q4 and therefore these show 0.

As the returns in each quarter include the totals from the previous quarters I don't want to add the cells to get a total, rather the most recent return will be the total (in this case Q2 as this is the last one to be received).

I was thinking an IF statement of some sort, but can't seem to get it to work, i.e.

IF B4 is 0 then show B3, but if B3 is 0 then show B2, but if B2 is 0 then show B1.

Any suggestions?

**DonkeyOte** · 12-15-2009, 09:55 AM

Without knowing how your cells B1:B4 is populated B5 would be:

=IF(B1=0,0,LOOKUP(2,1/(B1:B4<>0),B1:B4))

but this is of course assumes 0 is never a valid result....

another alternative if the returns always increase (again not clear)

=MAX(B1:B4)

what are the contents of B1:B4 - presumably formulae of some sort ?

**maybelle** · 12-15-2009, 09:57 AM

I would use if(index(rangeofyourvalues,count(rangeofyourvalues)=0,index(rangeofyourvalues,(count(rangeofyourvalues)-1),index(rangeofyourvalues,count(rangeofyourvalues)) this should show the last value entered and discount any blanks

**DonkeyOte** · 12-15-2009, 09:59 AM

re: maybelle's approach - I think you could adapt the COUNT to a COUNTIF (ie we're assuming 0 not blank) and thus use:

=IF(B1=0,0,INDEX($B$1:$B$4,COUNTIF($B$1:$B$4,"<>0")))

again though assuming 0 to never be valid

**maybelle** · 12-15-2009, 10:05 AM

Originally Posted by DonkeyOte

re: maybelle's approach - I think you could adapt the COUNT to a COUNTIF (ie we're assuming 0 not blank) and thus use:

=IF(B1=0,0,INDEX($B$1:$B$4,COUNTIF($B$1:$B$4,"<>0")))

again though assuming 0 to never be valid

that's much cleaner than what I posted.

Alternatively, if the formula in B1:B4 stipulates a "" result if there is no data for the last quarter, the simpler version of index(count()) works

**DonkeyOte** · 12-15-2009, 10:08 AM

Originally Posted by maybelle

Alternatively, if the formula in B1:B4 stipulates a "" result if there is no data for the last quarter, the simpler version of index(count()) works

If that were indeed the case:

Please Login or Register  to view this content.

though the question re: B1:B4 content has been asked it has not yet been answered.

**welshman1982** · 12-15-2009, 11:53 AM

The contents of cels B1 to B4 are a formula adding 3 cells from another spreadsheet to get a total for the quater i.e. B1 is the total of Jan, Feb and March, giving the total for Q1. As there are no returns from July onwards (Q3 & Q4) the value the formula gives is 0.

=SUM('Monitoring 2009-2010'!BB23,'Monitoring 2009-2010'!BB40,'Monitoring 2009-2010'!BB53)