Averaging Fiscal / Calendar year Fuel

M

mePenny

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

David Biddulph

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" <[email protected]> wrote in messagehave 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)>
 
E

Eduardo

Hi,

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

David Biddulph

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 said:
There is nothing different David. I'm needing seperate formula's for each
Click to expand...
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
 
E

Eduardo

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"))
 
E

Eduardo

Hi,
If you prefer you can send me the file and I will take a look to
(e-mail address removed)
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
 
D

David Biddulph

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fiscal Year Formula 7
Still in need of help 3
using fiscal year calculations in a formula 1
calendar year versus fiscal year 4

Top