PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Misc > Averaging Fiscal / Calendar year Fuel

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Averaging Fiscal / Calendar year Fuel

 
 
mePenny
Guest
Posts: n/a
 
      30th Nov 2009
I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?

mePenny

Calendar
=SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
=SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)

Fisca
=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)

=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
 
Reply With Quote
 
 
 
 
David Biddulph
Guest
Posts: n/a
 
      30th Nov 2009
If you want the average of the column C values that meet your criteria,
change your
=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)to =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)/SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d"))--David Biddulph"mePenny" <(E-Mail Removed)> wrote in messagenews:87D6B0E7-B388-4CC8-8412-(E-Mail Removed)...>I have the following formula's to seperate out fuel (u=unleaded, d=diesel)> for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for anaverage> of gallons of fuel formula for the Fiscal and Calendar year. Can anyonehelp?>> mePenny>> Calendar> =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)> =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)>> Fiscal>=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)>>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)>
 
Reply With Quote
 
Eduardo
Guest
Posts: n/a
 
      30th Nov 2009
Hi,

=AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
=AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")


"mePenny" wrote:

> I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
> of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?
>
> mePenny
>
> Calendar
> =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
>
> Fiscal
> =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
>
> =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
>
 
Reply With Quote
 
mePenny
Guest
Posts: n/a
 
      30th Nov 2009
There is nothing different David. I'm needing seperate formula's for each

"David Biddulph" wrote:

> If you want the average of the column C values that meet your criteria,
> change your
> =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)to =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)/SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d"))--David Biddulph"mePenny" <(E-Mail Removed)> wrote in messagenews:87D6B0E7-B388-4CC8-8412-(E-Mail Removed)...>I have the following formula's to seperate out fuel (u=unleaded, d=diesel)> for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for anaverage> of gallons of fuel formula for the Fiscal and Calendar year. Can anyonehelp?>> mePenny>> Calendar> =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)> =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)>>
Fiscal>=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)>>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)>
>
> .
>
 
Reply With Quote
 
mePenny
Guest
Posts: n/a
 
      30th Nov 2009
This tells me #NAME? for both

"Eduardo" wrote:

> Hi,
>
> =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
> =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")
>
>
> "mePenny" wrote:
>
> > I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> > for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
> > of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?
> >
> > mePenny
> >
> > Calendar
> > =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> > =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
> >
> > Fiscal
> > =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
> >
> > =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
> >
 
Reply With Quote
 
Eduardo
Guest
Posts: n/a
 
      30th Nov 2009
Hi,
I tested it and it's working , are you using excel 2007 ?
"mePenny" wrote:

> This tells me #NAME? for both
>
> "Eduardo" wrote:
>
> > Hi,
> >
> > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
> > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")
> >
> >
> > "mePenny" wrote:
> >
> > > I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> > > for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
> > > of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?
> > >
> > > mePenny
> > >
> > > Calendar
> > > =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> > > =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
> > >
> > > Fiscal
> > > =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
> > >
> > > =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
> > >
 
Reply With Quote
 
David Biddulph
Guest
Posts: n/a
 
      30th Nov 2009
Yes. So if you need a separate formula for each, you need to do the same
for your other formulae as I did for the one for which I gave you the
average.
--
David Biddulph


"mePenny" <(E-Mail Removed)> wrote in message
news:934F4A7B-20D3-4A9F-B0B6-(E-Mail Removed)...
> There is nothing different David. I'm needing seperate formula's for each
>
> "David Biddulph" wrote:
>
>> If you want the average of the column C values that meet your criteria,
>> change your
>>
>> =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
to
=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)/SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d"))

--David Biddulph

"mePenny" <(E-Mail Removed)>
wrote in messagenews:87D6B0E7-B388-4CC8-8412-(E-Mail Removed)...
>I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for anaverage
> of gallons of fuel formula for the Fiscal and Calendar year. Can
> anyonehelp?
>> mePenny
>> Calendar
> =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
>>
> Fiscal
>=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
>>=SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
>
>>
>> .
>>
 
Reply With Quote
 
mePenny
Guest
Posts: n/a
 
      30th Nov 2009
EXCEL 2002 SP3

"Eduardo" wrote:

> Hi,
> I tested it and it's working , are you using excel 2007 ?
> "mePenny" wrote:
>
> > This tells me #NAME? for both
> >
> > "Eduardo" wrote:
> >
> > > Hi,
> > >
> > > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
> > > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")
> > >
> > >
> > > "mePenny" wrote:
> > >
> > > > I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> > > > for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
> > > > of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?
> > > >
> > > > mePenny
> > > >
> > > > Calendar
> > > > =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> > > > =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
> > > >
> > > > Fiscal
> > > > =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
> > > >
> > > > =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
> > > >
 
Reply With Quote
 
Eduardo
Guest
Posts: n/a
 
      30th Nov 2009
Hi,
In that case try

=SUMPRODUCT((A6:A100=2009)*(B6:B100="u"),C6:C100)/SUMPRODUCT((A6:A100=2009)*(B6:B100="u"))

=SUMPRODUCT((A6:A100=2009)*(B6:B100="d"),C6:C100)/SUMPRODUCT((A6:A100=2009)*(B6:B100="d"))

"mePenny" wrote:

> EXCEL 2002 SP3
>
> "Eduardo" wrote:
>
> > Hi,
> > I tested it and it's working , are you using excel 2007 ?
> > "mePenny" wrote:
> >
> > > This tells me #NAME? for both
> > >
> > > "Eduardo" wrote:
> > >
> > > > Hi,
> > > >
> > > > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
> > > > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")
> > > >
> > > >
> > > > "mePenny" wrote:
> > > >
> > > > > I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> > > > > for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
> > > > > of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?
> > > > >
> > > > > mePenny
> > > > >
> > > > > Calendar
> > > > > =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> > > > > =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
> > > > >
> > > > > Fiscal
> > > > > =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
> > > > >
> > > > > =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
> > > > >
 
Reply With Quote
 
mePenny
Guest
Posts: n/a
 
      30th Nov 2009
It's telling me i have a divide by zero error #DIV/0

"Eduardo" wrote:

> Hi,
> In that case try
>
> =SUMPRODUCT((A6:A100=2009)*(B6:B100="u"),C6:C100)/SUMPRODUCT((A6:A100=2009)*(B6:B100="u"))
>
> =SUMPRODUCT((A6:A100=2009)*(B6:B100="d"),C6:C100)/SUMPRODUCT((A6:A100=2009)*(B6:B100="d"))
>
> "mePenny" wrote:
>
> > EXCEL 2002 SP3
> >
> > "Eduardo" wrote:
> >
> > > Hi,
> > > I tested it and it's working , are you using excel 2007 ?
> > > "mePenny" wrote:
> > >
> > > > This tells me #NAME? for both
> > > >
> > > > "Eduardo" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
> > > > > =AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")
> > > > >
> > > > >
> > > > > "mePenny" wrote:
> > > > >
> > > > > > I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
> > > > > > for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
> > > > > > of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?
> > > > > >
> > > > > > mePenny
> > > > > >
> > > > > > Calendar
> > > > > > =SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6:C100)
> > > > > > =SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6:C101)
> > > > > >
> > > > > > Fiscal
> > > > > > =SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100)>=7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6:B100="u")*C6:C100)
> > > > > >
> > > > > > =SUMPRODUCT((TEXT(A6:A100,"yyyymm")>="200907")*(TEXT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C100)
> > > > > >
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Changing Chart Tab Reference | Bug Found in Excel 2007 »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
calendar year versus fiscal year mePenny Microsoft Excel Misc 4 17th Nov 2009 06:05 PM
displaying the months on a graph by fiscal year not calendar year SMT Microsoft Access Queries 1 11th Feb 2008 10:59 PM
Help dealing with a fiscal year rather than a calendar year =?Utf-8?B?VHlsZXI=?= Microsoft Excel Worksheet Functions 3 25th Aug 2007 11:26 AM
calendar and fiscal year Giulio Defeudis Microsoft Excel Charting 1 22nd Feb 2004 09:00 AM
Grouping data by fiscal year not calendar year Ted Hall Microsoft Access 1 26th Nov 2003 11:36 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 01:26 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top