 |
 | 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 / October 2007Using formulas in conditions (SUMIF, AVERAGEIF)
Hi, I want to use formula in condition syntax, but I don't know how to reference the value being tested. Like: SUMIF(A1:A10;">10 AND <20") Or: AVERAGEIF(A1:A10;"ISODD(...)") Thanks for any ideas. Jan For your <<< SUMIF(A1:A10;">10 AND <20") >>> Try: =Sumif(A1:A10,">10")-Sumif(A1:A10,">=20) OR =SUMPRODUCT((A1:A10>10)*(A1:A10<20)*A1:A10)  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi, > I want to use formula in condition syntax, but I don't know how to [quoted text clipped - 9 lines] > > Jan Forgot a dbl quotes: =Sumif(A1:A10,">10")-Sumif(A1:A10,">=20") SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > For your <<< SUMIF(A1:A10;">10 AND <20") >>> > [quoted text clipped - 18 lines] > > > > Jan Hi Ragdyer, well, although this is nice math trick, is it impossible to combine expressions? Jan > Forgot a dbl quotes: > [quoted text clipped - 27 lines] >> > >> > Jan Don't you consider the expressions in the Sumproduct formula as a sort of combination?<bg> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Hi Ragdyer, > well, although this is nice math trick, is it impossible to combine [quoted text clipped - 39 lines] > >> > > >> > Jan For the sum if, try this: =SUMPRODUCT(--(A1:A10>=10),--(A1:A10<=20),A1:A10) For the average if is odd, try this array formula** : =AVERAGE(IF(MOD(A1:A10,2),A1:A10)) The ISODD/ISEVEN functions won't work on arrays. Also, I'm assuming the values in question are integers and there are no text entries in the range. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) If you're using Excel 2007 there is an AVERAGEIF function but I don't use Excel 2007 so I don't know the correct syntax. The above array formula will work in any version. SignatureBiff Microsoft Excel MVP > Hi, > I want to use formula in condition syntax, but I don't know how to [quoted text clipped - 9 lines] > > Jan Hello Biff, thank you for your reply. Unfortunately, the first solution uses math trick as well and the second one profits from being able to express the ISODD function using modulo 2. I am actually looking for general way to pass the tested value to a function, be it ISERR, ISBLANK, ISTEXT, WEEKDAY, SEARCH or whatever.... Thanks, Jan > For the sum if, try this: > [quoted text clipped - 28 lines] >> >> Jan No it's not possible, thus the answers you got SignatureRegards, Peo Sjoblom > Hello Biff, > thank you for your reply. Unfortunately, the first solution uses math [quoted text clipped - 37 lines] >>> >>> Jan Fair enough, thank you for answering. Jan > No it's not possible, thus the answers you got > [quoted text clipped - 39 lines] >>>> >>>> Jan Okay, is there a similar math trick for average of values in range? Like AVERAGEIF(B1:B10, >=1.1.2000 AND < 1.2.2000, A1:A10) Thanks, Jan > For the sum if, try this: > [quoted text clipped - 28 lines] >> >> Jan Can you elaborate on the figures that you're using as your criteria? If not typos, 1.1.2000 and 1.2.200 are what ... exactly? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Okay, > is there a similar math trick for average of values in range? [quoted text clipped - 34 lines] > >> > >> Jan They are dates from another regional setting so one should be able to use the DATE function as the criteria SignatureRegards, Peo Sjoblom > Can you elaborate on the figures that you're using as your criteria? > [quoted text clipped - 39 lines] >> >> >> >> Jan I might have guessed that if he had at least used 2007 somewhere in there, instead of making myself appear dense.<bg> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > They are dates from another regional setting so one should be able to use > the DATE function as the criteria [quoted text clipped - 46 lines] > >> >> > >> >> Jan Okay, sorry guys, did not realized that I use another locale, that was just for example to simplify the thing I'm trying to do. They are 1/1/2000 and 2/1/2000. Jan > I might have guessed that if he had at least used 2007 somewhere in there, > instead of making myself appear dense.<bg> [quoted text clipped - 54 lines] >> >> >> >> >> >> Jan One way would be to enter your date criteria in cells, that you can then reference in the formula, so that you could change the dates without having to change the actual formula itself. Say C1 = start date And C2 = end date, which in your case would be 31.1.2000: =SUMPRODUCT((B1:B10>=C1)*(B1:B10<=C2)*A1:A10)/SUMPRODUCT((B1:B10>=C1)*(B1:B10<C2)) Another way could be this *array* formula: =AVERAGE(IF((B1:B10>=C1)*(B1:B10<=C2),A1:A10)) SignatureArray formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of the regular <Enter>, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Okay, sorry guys, did not realized that I use another locale, that was > just for example to simplify the thing I'm trying to do. [quoted text clipped - 65 lines] >>> >> >> >>> >> >> Jan I don't have XL2007, but in XL2000 there is a conditional sum add-in (Tools/Add-Ins/Conditional Sum Wizard). I've never played w/it, but from the description in XL help, it appears that it will build the conditional sum array formula for you. It may be a good starting point. > Okay, > is there a similar math trick for average of values in range? [quoted text clipped - 34 lines] > >> > >> Jan
|
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.