I have a spreadsheet set up as follows:
Col A has Dates
Col B has production units
Col C has Energy Units
Etc

One row for every day of the year starting at row 2, through row 366.

What I want to do is sum the production and energy units for every day
that the production was greater than 5% of the average annual
production.

I have tried =COUNTIF($B$2:$B$366,AVERAGE($B$2:$B$366)*.05)
This results in error messages.

I tried using a seperate cell for the average,
E.g. cell B367 =AVERAGE($B$2:$B$366)*.05

Then using the wizard =COUNTIF($B$2:$B$366,B367)
changes to =COUNTIF($B$2:$B$366,"B367")
and the reference is not used.

What am I doing wrong?


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762

hi DC,

try

=sumproduct(--(B2:b366average(b2:b366)*5%))

hth
regards from Brazil
Marcelo

"DCSwearingen" escreveu:


I have a spreadsheet set up as follows:
Col A has Dates
Col B has production units
Col C has Energy Units
Etc

One row for every day of the year starting at row 2, through row 366.

What I want to do is sum the production and energy units for every day
that the production was greater than 5% of the average annual
production.

I have tried =COUNTIF($B$2:$B$366,AVERAGE($B$2:$B$366)*.05)
This results in error messages.

I tried using a seperate cell for the average,
E.g. cell B367 =AVERAGE($B$2:$B$366)*.05

Then using the wizard =COUNTIF($B$2:$B$366,B367)
changes to =COUNTIF($B$2:$B$366,"B367")
and the reference is not used.

What am I doing wrong?


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762

Try:
=COUNTIF($B$2:$B$366,""&AVERAGE($B$2:$B$366)*.05)

HTH
--
AP

"DCSwearingen" a
écrit dans le message de news:
...

I have a spreadsheet set up as follows:
Col A has Dates
Col B has production units
Col C has Energy Units
Etc

One row for every day of the year starting at row 2, through row 366.

What I want to do is sum the production and energy units for every day
that the production was greater than 5% of the average annual
production.

I have tried =COUNTIF($B$2:$B$366,AVERAGE($B$2:$B$366)*.05)
This results in error messages.

I tried using a seperate cell for the average,
E.g. cell B367 =AVERAGE($B$2:$B$366)*.05

Then using the wizard =COUNTIF($B$2:$B$366,B367)
changes to =COUNTIF($B$2:$B$366,"B367")
and the reference is not used.

What am I doing wrong?


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762

=sumproduct(--($b$2:$b$366average($b$2:$b$366)*.05),$b$2:$b$366 )


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762

answering your own questions?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DCSwearingen"
wrote in message
...

=sumproduct(--($b$2:$b$366average($b$2:$b$366)*.05),$b$2:$b$366 )


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762

Yes, I guess I did answer my own question.

Almost as soon as I posted I saw several other questions regarding
COUNT and COUNTIF, after reading them, I tried the SUMPRODUCT route.
Had to go to a couple of MVP references to see how the double minus
sign impacted the formula. Excel Help does not make that distinction
anywhere I could find.

My understanding is that basically the double minus sign, turns that
portion of the function into a more flexible IF function since it
returns a 1 for TRUE, and then the manipulation can be made for the
true tests.

Thank you for taking the time to read and respond to our issues...


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762

No, you are right, Help doesn't mention it. As you say the double unary
changes True to 1, False to 0, and it is then possible to multiply that
array of values by the array of 1/0 values that represent met/failed
conditions, and hence the result.

There is more detail at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
..

I bet you learnt more by working it out for yourself <g

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DCSwearingen"
wrote in message
news:DCSwearingen.2auh35_1152728107.8493@excelforu m-nospam.com...

Yes, I guess I did answer my own question.

Almost as soon as I posted I saw several other questions regarding
COUNT and COUNTIF, after reading them, I tried the SUMPRODUCT route.
Had to go to a couple of MVP references to see how the double minus
sign impacted the formula. Excel Help does not make that distinction
anywhere I could find.

My understanding is that basically the double minus sign, turns that
portion of the function into a more flexible IF function since it
returns a 1 for TRUE, and then the manipulation can be made for the
true tests.

Thank you for taking the time to read and respond to our issues...


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=560762