conditional minimum value of various cells?

[Guest]

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!!

 

[Guest]

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

 

[Biff]

Hi!

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

=MIN(IF(C1:C100="Driver",D1100))

Use the same syntax for each of the other functions.

Biff

 

[Guest]

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?

 

[Guest]

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!

 

[Bob Phillips]

Either

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

as an array formula again

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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

 

[Myles]

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

Myles

 

[Bob Phillips]

Yep.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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