sumif + average over12mths, then removing the avearge on 12mth 

Author 
Message 
AndyMorgan

Posted: Tue Apr 24 08:00:35 CDT 2007 
Top 
worksheet functions >> sumif + average over12mths, then removing the avearge on 12mth
I have 13 columns of data 112mths the first column calculates the totals of
mths 1 to 12. When I entre data say the 1st 4mths I need the 1st colum to
read the total actal spend and average so we can predict over/under spend
befor the year end. This bit I have achieved. PROBLEM when I get to the
12th month I want the formula to just look at the actual spend and not
include the aveage. Can this be done in one formula if so how
Thanks
Amy
Excel494





Bob

Posted: Tue Apr 24 08:00:35 CDT 2007 
Top 
worksheet functions >> sumif + average over12mths, then removing the avearge on 12mth
Amy,
Test whether all cells are input, and then calculate accordingly
=IF(COUNT(B2:M2)=12,...


HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
>I have 13 columns of data 112mths the first column calculates the totals
>of
> mths 1 to 12. When I entre data say the 1st 4mths I need the 1st colum to
> read the total actal spend and average so we can predict over/under spend
> befor the year end. This bit I have achieved. PROBLEM when I get to the
> 12th month I want the formula to just look at the actual spend and not
> include the aveage. Can this be done in one formula if so how
>
> Thanks
> Amy
>





Bernie

Posted: Tue Apr 24 08:05:29 CDT 2007 
Top 
worksheet functions >> sumif + average over12mths, then removing the avearge on 12mth
Amy,
=IF(Cell With 12th Month <> "",Cell With Actual Spend, Your Formula)
HTH,
Bernie
MS Excel MVP
>I have 13 columns of data 112mths the first column calculates the totals of
> mths 1 to 12. When I entre data say the 1st 4mths I need the 1st colum to
> read the total actal spend and average so we can predict over/under spend
> befor the year end. This bit I have achieved. PROBLEM when I get to the
> 12th month I want the formula to just look at the actual spend and not
> include the aveage. Can this be done in one formula if so how
>
> Thanks
> Amy
>





AmyD

Posted: Tue Apr 24 08:46:08 CDT 2007 
Top 
worksheet functions >> sumif + average over12mths, then removing the avearge on 12mth
Bernie the Brilliant
Has anyone ever told you 'YOUR A GENIUS' It worked I got that e****d my
work collegues want o put me in the nut house
Thanks for your help
from a very happy young lady
> Amy,
>
> =IF(Cell With 12th Month <> "",Cell With Actual Spend, Your Formula)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> >I have 13 columns of data 112mths the first column calculates the totals of
> > mths 1 to 12. When I entre data say the 1st 4mths I need the 1st colum to
> > read the total actal spend and average so we can predict over/under spend
> > befor the year end. This bit I have achieved. PROBLEM when I get to the
> > 12th month I want the formula to just look at the actual spend and not
> > include the aveage. Can this be done in one formula if so how
> >
> > Thanks
> > Amy
> >
>
>
>





Bernie

Posted: Tue Apr 24 08:57:51 CDT 2007 
Top 
worksheet functions >> sumif + average over12mths, then removing the avearge on 12mth
Amy,
> Bernie the Brilliant
Thanks. I've been called many names in my life, but I don't remember that being one ;)
I'm glad you were able to get it to work, and thanks for letting me know of your success.
Bernie
MS Excel MVP






