Help with IF & Average Functions

[Cybertech]

I'm a newbe having problems with the functions IF and AVERAGE. I have a spreadsheet with about 1322 rows. In Column "B" I have a value that ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am I doing wrong?

Thanks,

Tom

[Rowan]

One Way

=SUMIF(B:B,3,D:D)/COUNTIF(B:B,3)

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Cybertech]

Thanks!!!

To Max and kk for explaining my problem and showing how to enter an array.

To Rowan for showing me another way to arrive at the correct answer.

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[kk]

Hi Tom,

Your formula should be working. You need to press Ctrl+Shift+Enter to
confirm the formula.
Excel will places curly braces around your formula.

Take a look at here:
http://www.cpearson.com/excel/array.htm
http://www.emailoffice.com/excel/arrays-bobumlas.html

kk


"Cybertech" <[email protected]> wrote
in message news:[email protected]...

I'm a newbe having problems with the functions IF and AVERAGE. I have a
spreadsheet with about 1322 rows. In Column "B" I have a value that
ranges from 0-10. In Column "D" I have numerical data.

I want to average only those rows in Column "D" that have a certain
value in Column "B". I created the following formula:

=AVERAGE(IF(B1:B1322=3,D1:D1322))

All it returns is the average of all the numbers in Column "D". What am
I doing wrong?

Thanks,

Tom


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=398000

[Max]

You're welcome, Cybertech !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks!!!
>
> To Max and kk for explaining my problem and showing how to enter an
> array.
>
> To Rowan for showing me another way to arrive at the correct answer.
>

[Max]

"Cybertech" wrote:
....
> =AVERAGE(IF(B1:B1322=3,D1:D1322))
> All it returns is the average of all the numbers in Column "D".
> What am I doing wrong?

The formula should work properly,
but it's an array formula which needs to be array-entered,
i.e. press CTRL+SHIFT+ENTER,
instead of just pressing ENTER to confirm the formula

If you do it correctly,
Excel will wrap curly braces { } around the formula,
viz. if you look closely, it'll appear in the formula bar as:

{=AVERAGE(IF(B1:B1322=3,D1:D1322))}

(Do not type the curly braces in yourself !)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--