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 1-12mths 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 1-12mths 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 1-12mths 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 1-12mths 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