Hi

I have these numbers in a column

-0,05%
1,27%
1,22%
-0,89%
0,19%
0,26%
-0,19%
1,48%
0,77%
-1,11%
-1,20%
0,11%

1)

I want to single out the negative number - add them together - and
display the avarage in a cell

2)

In another cell I want to single out the positive numbers - add them
together - and display the avarage

Sorry if this has been asked before. Please be kind

Thanks!

Kitty

Use SUMIF to total your negative numbers and COUNTIF to get the divisor.

=SUMIF(A1:A7,"<0",A1:A7)/COUNTIF(A1:A7,"<0")
--
Kevin Backmann


"Kitty" wrote:

Hi

I have these numbers in a column

-0,05%
1,27%
1,22%
-0,89%
0,19%
0,26%
-0,19%
1,48%
0,77%
-1,11%
-1,20%
0,11%

1)

I want to single out the negative number - add them together - and
display the avarage in a cell

2)

In another cell I want to single out the positive numbers - add them
together - and display the avarage

Sorry if this has been asked before. Please be kind

Thanks!

Kitty

=AVERAGE(IF(A1:A12<0,A1:A12))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

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

"Kitty" wrote in message
...
Hi

I have these numbers in a column

-0,05%
1,27%
1,22%
-0,89%
0,19%
0,26%
-0,19%
1,48%
0,77%
-1,11%
-1,20%
0,11%

1)

I want to single out the negative number - add them together - and
display the avarage in a cell

2)

In another cell I want to single out the positive numbers - add them
together - and display the avarage

Sorry if this has been asked before. Please be kind

Thanks!

Kitty

On Tue, 10 Oct 2006 13:31:38 GMT, Kitty wrote:


Hi and thanks but I can't get it to work :( I think it maight have to
do with the syntax somehow I'm on a international pc with an english
version of excel 2003

I have made a fresh new file with the follwing numbers

Column <A
7,11
-0,52
13,47
12,72
-9,21
1,95
2,71
-1,95
15,45
7,85
-11,34
-12,39

This formula =SUM(A1:A12) works and gives the result
of 25,85 Note the comma as separator.

I can also do this =AVERAGE(A1:A12) which gives 2,15

But I can't do this =SUMIF(A1:A12,"0",A1:A12) "the formula you
typed...

=AVERAGE(IF(A1:A12<0,A1:A12)) also no good :(

Any ideas - Thanks

Click Tools and select ADD-INS and see if you have the Analysis Toolpack
running, if not click the checkbox to turn it on. The SUMIF and COUNTIF
should work once it's activated
--
Kevin Backmann


"Kitty" wrote:

On Tue, 10 Oct 2006 13:31:38 GMT, Kitty wrote:


Hi and thanks but I can't get it to work :( I think it maight have to
do with the syntax somehow I'm on a international pc with an english
version of excel 2003

I have made a fresh new file with the follwing numbers

Column <A
7,11
-0,52
13,47
12,72
-9,21
1,95
2,71
-1,95
15,45
7,85
-11,34
-12,39

This formula =SUM(A1:A12) works and gives the result
of 25,85 Note the comma as separator.

I can also do this =AVERAGE(A1:A12) which gives 2,15

But I can't do this =SUMIF(A1:A12,"0",A1:A12) "the formula you
typed...

=AVERAGE(IF(A1:A12<0,A1:A12)) also no good :(

Any ideas - Thanks

Maybe...

Maybe you have a list separator of semicolon (;) instead of comma (,).

Try changing the formula to:
=SUMIF(A1:A12;"0";A1:A12)
(same kind of thing with the other formulas, too)

===
If that's not it, I think you're going to have to explain what you mean.

Can you enter the formula?
Does it evaluate incorrectly?


Kitty wrote:

On Tue, 10 Oct 2006 13:31:38 GMT, Kitty wrote:

Hi and thanks but I can't get it to work :( I think it maight have to
do with the syntax somehow I'm on a international pc with an english
version of excel 2003

I have made a fresh new file with the follwing numbers

Column <A
7,11
-0,52
13,47
12,72
-9,21
1,95
2,71
-1,95
15,45
7,85
-11,34
-12,39

This formula =SUM(A1:A12) works and gives the result
of 25,85 Note the comma as separator.

I can also do this =AVERAGE(A1:A12) which gives 2,15

But I can't do this =SUMIF(A1:A12,"0",A1:A12) "the formula you
typed...

=AVERAGE(IF(A1:A12<0,A1:A12)) also no good :(

Any ideas - Thanks

--

Dave Peterson

=sumif() and =countif() aren't part of the Analysis Toolpak.

Kevin B wrote:

Click Tools and select ADD-INS and see if you have the Analysis Toolpack
running, if not click the checkbox to turn it on. The SUMIF and COUNTIF
should work once it's activated
--
Kevin Backmann

"Kitty" wrote:

On Tue, 10 Oct 2006 13:31:38 GMT, Kitty wrote:


Hi and thanks but I can't get it to work :( I think it maight have to
do with the syntax somehow I'm on a international pc with an english
version of excel 2003

I have made a fresh new file with the follwing numbers

Column <A
7,11
-0,52
13,47
12,72
-9,21
1,95
2,71
-1,95
15,45
7,85
-11,34
-12,39

This formula =SUM(A1:A12) works and gives the result
of 25,85 Note the comma as separator.

I can also do this =AVERAGE(A1:A12) which gives 2,15

But I can't do this =SUMIF(A1:A12,"0",A1:A12) "the formula you
typed...

=AVERAGE(IF(A1:A12<0,A1:A12)) also no good :(

Any ideas - Thanks








--

Dave Peterson

On Tue, 10 Oct 2006 13:31:38 GMT, Kitty wrote:


Matter solved - The machine is using semicolons like this

=SUMIF(A1:A7;"<0";A1:A7)/COUNTIF(A1:A7;"<0")

as list separator. So all the formulas are now working ;-)

Thanks for your time. Love you all,
Kitty