 |
 | 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 / New Users / December 2005MIN function
As described in another post of mine, I am calculating totals based on criteria. In this case, I'm using SUMIF to calculate totals using criteria that is between two numbers (>=1 and <=5 for instance). Here is my SUMIF formula: =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$A$336,">"&I2,Revised!$L$2:$L$336) What I now want to do is determine the Min, Max, Median, and Average of the cells that fall within the range specified by the parameters. Not sure how to go about doing this... =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$ 336)) =AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2 :$A$336)) etc  SignatureRegards, Peo Sjoblom > As described in another post of mine, I am calculating totals based on > criteria. In this case, I'm using SUMIF to calculate totals using criteria > that is between two numbers (>=1 and <=5 for instance). > > Here is my SUMIF formula: =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$ A$336,">"&I2,Revised!$L$2:$L$336) > What I now want to do is determine the Min, Max, Median, and Average of the > cells that fall within the range specified by the parameters. > > Not sure how to go about doing this... Hmmm. These don't work for me. In Sheet labelled Control, I have two columns to set parameters - column A is the first range and column B is the second range. A B Min Max Mean 1 5 6 10 11 15 On the Sheet labelled Verified is my data. Column A contains a number, column L contains a dollar amount. A ... L 4 $1525 What I am attemting to do is get the MIN, MAX, MEAN, and MODE for the values in Verified column L where the values in Verified column A fall in the range I specify using columns A and B on the Control sheet. Does this make sense? > =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$ > 336)) [quoted text clipped - 19 lines] >> >> Not sure how to go about doing this... Use the same formula but replace the range after the comma with the second range, i.e. =MIN(IF((RangeA>=x)*(RangeA<=y),RangeL)) entered with ctrl + shift & enter SignatureRegards, Peo Sjoblom > Hmmm. These don't work for me. > [quoted text clipped - 17 lines] > > Does this make sense? =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$ > > 336)) =AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2 > > :$A$336)) > > [quoted text clipped - 6 lines] > >> > >> Here is my SUMIF formula: =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$ > > A$336,">"&I2,Revised!$L$2:$L$336) > >> [quoted text clipped - 3 lines] > >> > >> Not sure how to go about doing this... Thanks again. I don't understand the use of the asterisk (*) in the formula. I made your suggested correction but the result of the formula is incorrect. The formula you provided is averaging the entire column L rather than just average those rows whose column A falls within the set criteria (established on sheet Control: A2 and B2) > Use the same formula but replace the range after the comma with the second > range, i.e. [quoted text clipped - 52 lines] >> >> >> >> Not sure how to go about doing this... It works believe me, did you enter it with ctrl + shift & enter? =AVERAGE(IF((RangeA>=x)*(RangeA<=y),RangeL)) will average values in RangeL where RangeA is greater than or equal to x AND less than or equal to y note that it if it is not entered with ctrl + shift & enter it will average the whole RangeL (look in help for array formulas) SignatureRegards, Peo Sjoblom > Thanks again. I don't understand the use of the asterisk (*) in the formula. > I made your suggested correction but the result of the formula is incorrect. [quoted text clipped - 34 lines] > >> > >> Does this make sense? =MIN(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2:$A$ > >> > 336)) =AVERAGE(IF((Revised!$A$2:$A$336>=G2)*(Revised!$A$2:$A$336<=I2),Revised!$A$2 > >> > :$A$336)) > >> > [quoted text clipped - 6 lines] > >> >> > >> >> Here is my SUMIF formula: =SUMIF(Revised!$A$2:$A$336,">="&G2,Revised!$L$2:$L$336)-SUMIF(Revised!$A$2:$ > >> > A$336,">"&I2,Revised!$L$2:$L$336) > >> >> [quoted text clipped - 4 lines] > >> >> > >> >> Not sure how to go about doing this... Peo - you are absolutely correct. I neglected to enter the formula as an array. THANK YOU! Your assistance is most appreciated! > It works believe me, did you enter it with ctrl + shift & enter? > [quoted text clipped - 79 lines] >> >> >> >> >> >> Not sure how to go about doing this... |
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.