conditional minimum value of various cells?
 

I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

There may be better ways, but one way could be something like:

=MIN(IF($C$2:$C$100="engineer",$D$2:$D$100,""))

where your data is in rows 2 through 100.

You can use the same basic formula for MAX, MEDIAN, MODE, and AVERAGE (and
obviously for your other job functions)

NB: this formula must be array-entered (ctrl-shift-enter)

HTH,
Ryan

"Al" wrote:

I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(C1:C100="Driver",D1:D100))

Use the same syntax for each of the other functions.

Biff

"Al" wrote in message
...
I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C =
"driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

That's great...it works a treat! I was not familiar with array-entering
(ctrl-shift-enter) prior to this.....what actually does this mean?

"Ryan Poth" wrote:

There may be better ways, but one way could be something like:

=MIN(IF($C$2:$C$100="engineer",$D$2:$D$100,""))

where your data is in rows 2 through 100.

You can use the same basic formula for MAX, MEDIAN, MODE, and AVERAGE (and
obviously for your other job functions)

NB: this formula must be array-entered (ctrl-shift-enter)

HTH,
Ryan

"Al" wrote:

I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

Another question please....if I have another condition, how do I do this? Say
I have another column of nationalities, and I then need to calc the min/max,
etc of American drivers, English drivers and Australian drivers?

I tried to nest some "if" functions....but screwed it up......hopefully
you'll have the answer!

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(C1:C100="Driver",D1:D100))

Use the same syntax for each of the other functions.

Biff

"Al" wrote in message
...
I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C =
"driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

Either

=MIN(IF((C1:C100="Driver")*(B1:B100="English"),D1: D100))

as an array formula again

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Al" wrote in message
...
Another question please....if I have another condition, how do I do this?
Say
I have another column of nationalities, and I then need to calc the
min/max,
etc of American drivers, English drivers and Australian drivers?

I tried to nest some "if" functions....but screwed it up......hopefully
you'll have the answer!

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(C1:C100="Driver",D1:D100))

Use the same syntax for each of the other functions.

Biff

"Al" wrote in message
...
I have two columns: column C has job functions, say engineer, cook,
driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode,
average
values for each of the job functions. (e.g., if data in column C =
"driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

Presumably if I want a 3rd condition I add another *(E1:E100="male") ??

"Bob Phillips" wrote:

Either

=MIN(IF((C1:C100="Driver")*(B1:B100="English"),D1: D100))

as an array formula again

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Al" wrote in message
...
Another question please....if I have another condition, how do I do this?
Say
I have another column of nationalities, and I then need to calc the
min/max,
etc of American drivers, English drivers and Australian drivers?

I tried to nest some "if" functions....but screwed it up......hopefully
you'll have the answer!

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(C1:C100="Driver",D1:D100))

Use the same syntax for each of the other functions.

Biff

"Al" wrote in message
...
I have two columns: column C has job functions, say engineer, cook,
driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode,
average
values for each of the job functions. (e.g., if data in column C =
"driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

As an alternative, *Database Functions *are squarely cut for these sort
of tasks.

Myles


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=500524

conditional minimum value of various cells?
 

Yep.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Al" wrote in message
...
Presumably if I want a 3rd condition I add another *(E1:E100="male") ??

"Bob Phillips" wrote:

Either

=MIN(IF((C1:C100="Driver")*(B1:B100="English"),D1: D100))

as an array formula again

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Al" wrote in message
...
Another question please....if I have another condition, how do I do
this?
Say
I have another column of nationalities, and I then need to calc the
min/max,
etc of American drivers, English drivers and Australian drivers?

I tried to nest some "if" functions....but screwed it
up......hopefully
you'll have the answer!

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(C1:C100="Driver",D1:D100))

Use the same syntax for each of the other functions.

Biff

"Al" wrote in message
...
I have two columns: column C has job functions, say engineer, cook,
driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode,
average
values for each of the job functions. (e.g., if data in column C =
"driver",
then corresponding salary to be included in the data to be
analysed.)

Hope someone out there has the answer!!

conditional minimum value of various cells?
 

Sorry for the delayed response, but I only haunt these NGs once a day at
most. I guess you already have an answer to this question (along with a
slightly shorter formula from Biff), but in case you don't here is an exerpt
from Excel's help files:

An array formula can perform multiple calculations and then return either a
single result or multiple results. Array formulas act on two or more sets of
values known as array arguments. Each array argument must have the same
number of rows and columns. You create array formulas in the same way that
you create other formulas, except you press CTRL+SHIFT+ENTER to enter the
formula.

Ryan

"Al" wrote:

That's great...it works a treat! I was not familiar with array-entering
(ctrl-shift-enter) prior to this.....what actually does this mean?

"Ryan Poth" wrote:

There may be better ways, but one way could be something like:

=MIN(IF($C$2:$C$100="engineer",$D$2:$D$100,""))

where your data is in rows 2 through 100.

You can use the same basic formula for MAX, MEDIAN, MODE, and AVERAGE (and
obviously for your other job functions)

NB: this formula must be array-entered (ctrl-shift-enter)

HTH,
Ryan

"Al" wrote:

I have two columns: column C has job functions, say engineer, cook, driver,
etc; column D has salaries.

I want to analyse the salaries: what's the min/max,median, mode, average
values for each of the job functions. (e.g., if data in column C = "driver",
then corresponding salary to be included in the data to be analysed.)

Hope someone out there has the answer!!