 |
 | 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 / November 2006problem with combining functions
I'm using regular Excel formula in the cells (not scripting, mainly because I don't know how). I have a problem with trying to combine offset() and small() my actual code is: =OFFSET(small(E4:E18,1),0,-2) and it obviously doesn't work. Is there any way to use those two features together? If it helps I'm trying to make a gradebook capable of weighting categories (e.g. 50% of avg for quiz, etc.) and dropping lowest quizzes. I have all the code working except this one combination of finding the lowest grade using small() and returning its corresponding number using offset(). Thanks in advance for any help. Marcas Burnett marcas91[at]gmail[dot]com Hi! The first argument in Offset must be a cell address. See if this does what you want: =OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2) Biff > I'm using regular Excel formula in the cells (not scripting, mainly > because I don't know how). I have a problem with trying to combine [quoted text clipped - 18 lines] > Marcas Burnett > marcas91[at]gmail[dot]com The only thing is: will the match function work if two grades can be the same? (if you can make 2 95's then will the match function work?) > Hi! > [quoted text clipped - 27 lines] > > Marcas Burnett > > marcas91[at]gmail[dot]com Ok, actually in any instance I could find in my grades this worked. I have two questions: 1. Why does putting the reference of E4 work when your reference is actually the entire array? 2. Also, Why is the "rows" of the offset -1 when it's in the same row...whenever I change it to 0, however, it returns the wrong value? (Using yours, it works, THANKS SO MUCH!!!!!!!!!!) > Hi! > [quoted text clipped - 27 lines] > > Marcas Burnett > > marcas91[at]gmail[dot]com =OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2) >Why does putting the reference of E4 work when your reference is >actually the entire array? The reference is not the entire range. The "starting point" is E4. MATCH(SMALL(E4:E18,1),E4:E18,0)-1 tells it how many rows to offset E4 -2 tells it how many columns to offset E4. >Why is the "rows" of the offset -1 when it's in the same >row...whenever I change it to 0, however, it returns >the wrong value? Because the MATCH function will only return values >=1. If the match value is in the first position the result would be 1 which would mean to offset E4 by 1 row but this would lead to an incorrect reference. In that case you want the offset to be 0 so in effect you need to "offset" the result of the MATCH function by -1. See Roger's reply. His solution also works and may be less complicated. Biff > Ok, actually in any instance I could find in my grades this worked. I > have two questions: [quoted text clipped - 38 lines] >> > Marcas Burnett >> > marcas91[at]gmail[dot]com Thank you so much! > =OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2) > [quoted text clipped - 63 lines] > >> > Marcas Burnett > >> > marcas91[at]gmail[dot]com You're welcome! Biff > Thank you so much! >> =OFFSET(E4,MATCH(SMALL(E4:E18,1),E4:E18,0)-1,-2) [quoted text clipped - 68 lines] >> >> > Marcas Burnett >> >> > marcas91[at]gmail[dot]com |
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.