Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
There's probably a way to do this with a formula, but another way is to use
Filters. I think this should work.... Apply filter to those columns then simply filter Column A to show only those with HOV; filter column B for STK; filter column K for SELL, then filter column N for the lowest value. Rob "Dallman Ross" <dman@localhost. wrote in message ... I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
Try this array* formula:
=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10) All ranges must be the same size - I've assumed you have 1000 elements. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote: I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
Sorry, missed a bracket from the end:
=MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)) Pete On Dec 8, 2:00 am, Pete_UK wrote: Try this array* formula: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10) All ranges must be the same size - I've assumed you have 1000 elements. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote: I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys.- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
In ,
Pete_UK spake thusly: Sorry, missed a bracket from the end: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)) Looks promising, Pete, but I'm getting a #NAME? error. I'm thinking the problem may lie with the "*" between text columns. Don't we need a SUMPRODUCT in there somewhere, anyway? Dallman ------------------------ On Dec 8, 2:00 am, Pete_UK wrote: Try this array* formula: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10) All ranges must be the same size - I've assumed you have 1000 elements. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote: I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
In , RobN
spake thusly: There's probably a way to do this with a formula, but another way is to use Filters. Rob, thanks for the ideas. I need a formula, because I am performing further operations/calculations on the resulting value. I do already use filters as you suggested. The formula I use will be in a separate workbook, by the way. It's not a problem to have both books open if necessary, though. Thanks for the input, dman --------------- I think this should work.... Apply filter to those columns then simply filter Column A to show only those with HOV; filter column B for STK; filter column K for SELL, then filter column N for the lowest value. Rob "Dallman Ross" <dman@localhost. wrote in message ... I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
No, you don't need a sumproduct - the * is equivalent to AND.
Basically the formula is saying if all three conditions are met, then take the value from column N otherwise take a very large value, and do this for every cell in the ranges. Then, with the array built up, take the minimum of those numbers. If you are getting #NAME? error, then you may have mis-typed MIN or IF, or you may have missed the quotes around "HOV", "STK" or "SELL", or you may have missed the : from between one of the ranges, or missed one of the brackets. Don't forget, you must commit the formula with CSE after you amend it. Hope this helps. Pete On Dec 8, 12:40 pm, Dallman Ross <dman@localhost. wrote: In , Pete_UK spake thusly: Sorry, missed a bracket from the end: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)-) Looks promising, Pete, but I'm getting a #NAME? error. I'm thinking the problem may lie with the "*" between text columns. Don't we need a SUMPRODUCT in there somewhere, anyway? Dallman ------------------------ On Dec 8, 2:00 am, Pete_UK wrote: Try this array* formula: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10) All ranges must be the same size - I've assumed you have 1000 elements. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote: I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
In ,
Pete_UK spake thusly: No, you don't need a sumproduct - the * is equivalent to AND. Basically the formula is saying if all three conditions are met, then take the value from column N otherwise take a very large value, and do this for every cell in the ranges. Then, with the array built up, take the minimum of those numbers. I got it to work now! Thanks again, Pete. Not entirely sure what I did wrong the first time. I will not for those following along that an errant "-" got intorduced in your correction, though. I took it out. I also changed "10^10" to "" -- I am content to have nothing there if there is now price for the item in question. Very helpful -- thanks! Dallman ------------------------------------------ If you are getting #NAME? error, then you may have mis-typed MIN or IF, or you may have missed the quotes around "HOV", "STK" or "SELL", or you may have missed the : from between one of the ranges, or missed one of the brackets. Don't forget, you must commit the formula with CSE after you amend it. Hope this helps. Pete On Dec 8, 12:40 pm, Dallman Ross <dman@localhost. wrote: In , Pete_UK spake thusly: Sorry, missed a bracket from the end: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)-) Looks promising, Pete, but I'm getting a #NAME? error. I'm thinking the problem may lie with the "*" between text columns. Don't we need a SUMPRODUCT in there somewhere, anyway? Dallman ------------------------ On Dec 8, 2:00 am, Pete_UK wrote: Try this array* formula: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10) All ranges must be the same size - I've assumed you have 1000 elements. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote: I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional MIN value
I'm glad you got it working - thanks for feeding back.
You sometimes get spurious line-breaks with long formulae in the newsgroups, and sometimes a hyphen gets included, depending on what you are using to view the posts. Pete On Dec 8, 3:31 pm, Dallman Ross <dman@localhost. wrote: In , Pete_UK spake thusly: No, you don't need a sumproduct - the * is equivalent to AND. Basically the formula is saying if all three conditions are met, then take the value from column N otherwise take a very large value, and do this for every cell in the ranges. Then, with the array built up, take the minimum of those numbers. I got it to work now! Thanks again, Pete. Not entirely sure what I did wrong the first time. I will not for those following along that an errant "-" got intorduced in your correction, though. I took it out. I also changed "10^10" to "" -- I am content to have nothing there if there is now price for the item in question. Very helpful -- thanks! Dallman ------------------------------------------ If you are getting #NAME? error, then you may have mis-typed MIN or IF, or you may have missed the quotes around "HOV", "STK" or "SELL", or you may have missed the : from between one of the ranges, or missed one of the brackets. Don't forget, you must commit the formula with CSE after you amend it. Hope this helps. Pete On Dec 8, 12:40 pm, Dallman Ross <dman@localhost. wrote: In , Pete_UK spake thusly: Sorry, missed a bracket from the end: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10)--) Looks promising, Pete, but I'm getting a #NAME? error. I'm thinking the problem may lie with the "*" between text columns. Don't we need a SUMPRODUCT in there somewhere, anyway? Dallman ------------------------ On Dec 8, 2:00 am, Pete_UK wrote: Try this array* formula: =MIN(IF((A1:A1000="HOV")*(B1:B1000="STK")*(K1:K100 0="SELL"),N1:N1000,10^10) All ranges must be the same size - I've assumed you have 1000 elements. * As this is an array formula, then once you have typed it in (or subsequently edit it) you must use CTRL-SHIFT-ENTER (CSE) to commit it, rather than the normal ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you must not type these yourself. Hope this helps. Pete On Dec 8, 1:23 am, Dallman Ross <dman@localhost. wrote: I'm stuck and could use some help. I have a table with lots of columns, one of which -- N -- is a price. I want to find the minimum price where the value in Column A is "HOV" and Col. B is "STK" and Col. K is "SELL". Thanks for the quick hand at this, guys.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
In essence, writing a formula that returns the
Hello Pete. I saw your recent help which relates to my current qestion.
In essence, writing a formula that returns the: Sum Average Min Max Standard Deviation If a value in a dataset fromn one sheet matches a value in another dataset. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
Conditional sum | Excel Discussion (Misc queries) | |||
Conditional DAY | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Conditional Sum | Excel Discussion (Misc queries) |