 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Minimum Ifs Function
I need a formula that would work as a minimum ifs function similar to the sumifs formula. Given the data set below I need the minimum list price for the group in cell A1 but only only if the Use = Y and Finish = Red. Can anyone help? Group Use Finish List Price Min List Price A X Red $10 B Y Blue $12 A Y Red $15 $15 C X Yellow $10 A Y Red $19 $15 B Y Red $9 Are the words Group, Use, Finish and List_Price defined names? (eg from row 3 onwards?) If so you can try this array* function: =MIN(IF((Group=$A$1)*(Use="X")*(Finish="Red"),List_Price,10E10)) An aray function must be committed with the key combination CTRL-SHIFT- ENTER (CSE) instead of the usual ENTER. Hope this helps. Pete On May 6, 5:13 pm, Jon Ratzel <JonRat...@discussions.microsoft.com> wrote: > I need a formula that would work as a minimum ifs function similar to the > sumifs formula. Given the data set below I need the minimum list price for [quoted text clipped - 8 lines] > A Y Red $19 $15 > B Y Red $9 Hi Pete, thanks for helping. I'm not sure if I understand your question. The words in the cells in the Group, Use, and Finish columns are defined names that I would normally enter as criteria in quotations as "Red" or "Y" if I were using a sumifs formula. The List Price changes with each row of data and would be like the sum range part of a sumifs formula. When I tried your formula and the one from T. Valko of the next post I didn't get the correct answer. I'm able to check everything by using a pivot table and vlookups but it's a very manual process, so I'm hoping an array formula would speed things up. Any other suggestions? > Are the words Group, Use, Finish and List_Price defined names? (eg > from row 3 onwards?) If so you can try this array* function: [quoted text clipped - 22 lines] > > A Y Red $19 $15 > > B Y Red $9 Do the named ranges cover the same number of rows? Did you commit the formula using CSE? (i.e. can you see curly braces { } around the formula when viewed in the formula bar?) Was the answer you got anywhere near the correct answer? Do you have any spaces at the end of any of the Red's or X's or Y's? Pete On May 6, 6:32 pm, Jon Ratzel <JonRat...@discussions.microsoft.com> wrote: > Hi Pete, thanks for helping. > [quoted text clipped - 37 lines] > > - Show quoted text - Hi Pete, The named ranges are covering the entire column. I'm also using regular parethesis and not the {}'s. I'm getting $0 from the formula instead of numbers ranging from $216 or $329. I don't have any spaces after any of the finishes like Red or Blue either. Thanks again for the help! Jon > Do the named ranges cover the same number of rows? > [quoted text clipped - 50 lines] > > > > - Show quoted text - You can't use full-column references in array formulae in Excel versions before 2007, so you will need to make your named ranges shorter (eg A2:A65536). When you type in an array formula you need to use the key combination of CTRL-SHIFT-ENTER instead of the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you do NOT type these yourself. If you can't see any curly braces in the formula bar, however, it means that you did not commit the formula as an array. Hope this helps. Pete On May 8, 5:43 pm, Jon Ratzel <JonRat...@discussions.microsoft.com> wrote: > Hi Pete, > [quoted text clipped - 62 lines] > > - Show quoted text - Try this array formula** : =MIN(IF((Group="A")*(Use="Y")*(Finish="Red"),Price)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)  SignatureBiff Microsoft Excel MVP >I need a formula that would work as a minimum ifs function similar to the > sumifs formula. Given the data set below I need the minimum list price for [quoted text clipped - 8 lines] > A Y Red $19 $15 > B Y Red $9 With the suppied data in A1:D7 try: =MIN(IF(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C2),$D$2:$D$7)) which is an arroy formula which shpould be entered with Ctrl + Shift + Enter not just Enter. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk >I need a formula that would work as a minimum ifs function similar to the > sumifs formula. Given the data set below I need the minimum list price for [quoted text clipped - 8 lines] > A Y Red $19 $15 > B Y Red $9 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.