ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Averaging Fiscal / Calendar year Fuel (https://www.excelbanter.com/excel-discussion-misc-queries/249613-averaging-fiscal-calendar-year-fuel.html)

mePenny
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)

David Biddulph[_2_]
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)

Eduardo
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)

mePenny
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)

.

mePenny
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)

Eduardo
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)

David Biddulph[_2_]
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)


.

mePenny
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)

Eduardo
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)

mePenny
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)

Eduardo
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)

Eduardo
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)

David Biddulph[_2_]
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)


All times are GMT +1. The time now is 05:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com