Minimum With Two Criteria

[goss]

Hi all,

I would like to run a variety of functions against a payrate table
But I can't seem to get the first function to work
The formula is returning 0, but should return $9.00
The table is setup correctly with no breaks in data
I visibly reviewed all items since HR sent me the table-all looks
correct

Do you see any errors in my formula below or any other ideas?

Thanks!
goss

=SUMPRODUCT(MIN((tbl!A$2:A$257=$A$1)*(tbl!$D$2:$D$257=$A4)*(tbl!$E$2:$E
$257)))

Where $A$1 = New Hire (sp is correct copied from tblPayRate)
Where $A4 = Cashier (sp is correct, copied from tblPayRate)
Where Col A is "New Hire" or other designation
Where Col D is "Cashier" or other position
Where Col E is range of values

 

[Dave Peterson]

The factors that look like this:
tbl!a2:a257=$a$1
will return an array of true/falses.

Multiplying them together will give you an array of 0's and 1's (1 when both
things are true at the same time).

So if the values in e2:e257 are all non-negative, the smallest value you'll see
is 0 (0 times anything will be 0).

I think you want:

=MIN(IF((Tbl!A$2:A$257=$A$1)*(Tbl!$D$2:$D$257=$A4),Tbl!$E$2:$E$257))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

 

[goss]

The factors that look like this:
tbl!a2:a257=$a$1
will return an array of true/falses.

Multiplying them together will give you an array of 0's and 1's (1 when both
things are true at the same time).

So if the values in e2:e257 are all non-negative, the smallest value you'll see
is 0 (0 times anything will be 0).

I think you want:

=MIN(IF((Tbl!A$2:A$257=$A$1)*(Tbl!$D$2:$D$257=$A4),Tbl!$E$2:$E$257))

This is an array formula.  Hit ctrl-shift-enter instead of enter.  Ifyou do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks Dave,

That worked.
Im pretty sure I read somewhere where the same can be accomplished
with the sumproduct stuff, but no matter

I was successfule with MIN and MAX ubt when I replaced with MEAN,
MEDIAN, MODE, I receive #NUM! errors

Can I do same with those functions?

Thanks!
Winston

 

[Dave Peterson]

Use =average(), not =mean().

I'm not sure why =median() didn't work for you. Did you remember to use
ctrl-shift-enter?

But from the help on =mode()
If the data set contains no duplicate data points, MODE returns the #N/A error
value.

Are you sure you have at least one value that is duplicated?

goss wrote: