Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Averaging Fiscal / Calendar year Fuel
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 38
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,651
Default Averaging Fiscal / Calendar year Fuel
If you want the average of the column C values that meet your criteria,
change your
=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)to =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)/SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TEX T(A6:A100,"yyyymm")<="201006")*(B6:B100="d"))--David Biddulph"mePenny" wrote in ...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:A1 00,"yyyymm")="200907")*(TEXT(A6:A100,"yyyymm")<=" 201006")*(B6:B100="d")*C6:C100)

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,276
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 38
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)to =SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)/SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TEX T(A6:A100,"yyyymm")<="201006")*(B6:B100="d"))--David Biddulph"mePenny" wrote in ...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:A1 00,"yyyymm")="200907")*(TEXT(A6:A100,"yyyymm")<=" 201006")*(B6:B100="d")*C6:C100)

.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 38
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,276
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,651
Default Averaging Fiscal / Calendar year Fuel
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" wrote in message
...
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)
to
=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)/SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TEX T(A6:A100,"yyyymm")<="201006")*(B6:B100="d"))

--David Biddulph

"mePenny"
wrote in ...
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 QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 38
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,276
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 38
Default Averaging Fiscal / Calendar year Fuel
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,276
Default Averaging Fiscal / Calendar year Fuel
Hi, could you please send a sample of your data, thanks

"mePenny" wrote:

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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,276
Default Averaging Fiscal / Calendar year Fuel
Hi,
If you prefer you can send me the file and I will take a look to

Please include in the subject line this post, In the email specify what you
want to achieve
I will take a look when I arrive home and will come back to you tomorrow
morning

"mePenny" wrote:

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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)

Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 8,651
Default Averaging Fiscal / Calendar year Fuel
That's because you left out the YEAR function from the column A part of your
formula. You don't have any values equal to 2009 in column A, so #DIV/0! is
what you'd expect from that formula.

Go back to your original formulae, and work from there.
--
David Biddulph

"mePenny" wrote in message
...
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")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
calendar year versus fiscal year mePenny Excel Discussion (Misc queries) 4 November 17th 09 06:05 PM
Define fiscal year TeeLt Excel Worksheet Functions 3 July 16th 08 10:01 PM
Help dealing with a fiscal year rather than a calendar year Tyler Excel Worksheet Functions 3 August 25th 07 11:26 AM
Fiscal Year Calculation DaGo21 Excel Worksheet Functions 13 February 7th 06 10:16 AM
Fiscal Calendar Eileen Excel Discussion (Misc queries) 1 November 27th 04 09:29 AM


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement