Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Excel-only average cells if two cells in same row, meet two condit

I want to average only some of the values in a column, based on tests on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use the
numeric value in cell A1 in the average. If these two conditions are not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,047
Default Excel-only average cells if two cells in same row, meet two condit

hi Eulie,

I'm not sure if I understand exactly you are looking for, but try it:

=sumproduct(--(b2:b100="ORD")*(c1:c100="MIA"),(a1:a100))/sumproduct((--(b2:b100="ORD")*(c1:c100="MIA"))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Eulie-Denver" escreveu:

I want to average only some of the values in a column, based on tests on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use the
numeric value in cell A1 in the average. If these two conditions are not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 380
Default Excel-only average cells if two cells in same row, meet two condit

=AVERAGE(IF((B1:B10="ORD")*(C1:C10="MIA"),A1:A10))

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Eulie-Denver" wrote in message
...
I want to average only some of the values in a column, based on tests on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use the
numeric value in cell A1 in the average. If these two conditions are not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Excel-only average cells if two cells in same row, meet two co

Yes this is exactly what I wanted to do. I cut and pasted it and changed to
the actual columns and number of columns and it worked. I'll have to read up
a bit more on array formulas though to fully understand. The asterisk must
mean 'AND'.
Thanks, this was great help.

"Bob Phillips" wrote:

=AVERAGE(IF((B1:B10="ORD")*(C1:C10="MIA"),A1:A10))

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Eulie-Denver" wrote in message
...
I want to average only some of the values in a column, based on tests on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use the
numeric value in cell A1 in the average. If these two conditions are not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Excel-only average cells if two cells in same row, meet two co

Thank you for taking time to help. The solution from Bob Philips worked for
me.
Eulie

"Marcelo" wrote:

hi Eulie,

I'm not sure if I understand exactly you are looking for, but try it:

=sumproduct(--(b2:b100="ORD")*(c1:c100="MIA"),(a1:a100))/sumproduct((--(b2:b100="ORD")*(c1:c100="MIA"))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Eulie-Denver" escreveu:

I want to average only some of the values in a column, based on tests on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use the
numeric value in cell A1 in the average. If these two conditions are not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 380
Default Excel-only average cells if two cells in same row, meet two co

* certainly does mean AND.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Eulie-Denver" wrote in message
...
Yes this is exactly what I wanted to do. I cut and pasted it and changed

to
the actual columns and number of columns and it worked. I'll have to read

up
a bit more on array formulas though to fully understand. The asterisk

must
mean 'AND'.
Thanks, this was great help.

"Bob Phillips" wrote:

=AVERAGE(IF((B1:B10="ORD")*(C1:C10="MIA"),A1:A10))

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

not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Eulie-Denver" wrote in message
...
I want to average only some of the values in a column, based on tests

on
other cells in the same row.

For example:
Suppose I have numeric values in A1:A10 and alpha characters in

columns
B1:B10 and C1:C10.
If the value in cell B1="ORD" and the value in cell C1="MIA" then use

the
numeric value in cell A1 in the average. If these two conditions are

not
TRUE then treat cell A1 as blank.
I want this same check to apply to each row.
So I want to average only the An cells that meet this condition.

Can anyone help me code this function?






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average only cells that contain numbers April Excel Worksheet Functions 3 April 24th 23 09:07 AM
omiting cells in average calcs Blackstar79 Excel Discussion (Misc queries) 3 April 25th 06 11:12 PM
Excel 2003, which formula will count the cells that meet 2 conditi lawoman35 Excel Worksheet Functions 4 March 23rd 06 10:27 PM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Merging cells in Excel 2003 Bob Excel Discussion (Misc queries) 1 October 19th 05 07:10 PM


All times are GMT +1. The time now is 09:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"