Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum With Two Criteria
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum With Two Criteria
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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum With Two Criteria
On Nov 3, 11:10*am, Dave Peterson wrote:
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 wrote: 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- 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Minimum With Two Criteria
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: <<snipped 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minimum IF | Excel Worksheet Functions | |||
Minimum Value | Excel Worksheet Functions | |||
How to lookup the minimum, 2nd minimum and 3rd minimum......... | Excel Worksheet Functions | |||
find minimum of range based on multiple criteria | Excel Worksheet Functions | |||
Minimum and Maximum with Complex Criteria | Excel Discussion (Misc queries) |