copying a formula down wards
in [Functions]
|
Prev: Rounding Up question
Next: #N/A help please
From: Steve Dunn on 16 Apr 2010 06:43 Too many arguments is referring to this part of your formula: IF(F$7:F$198, $AQ$6, B$7:B$198, 0) because an IF function only requires 3 arguments. I suspect you meant: IF(F$7:F$198=$AQ$6,B$7:B$198,0) "UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message news:7BC729F1-52FA-4C18-959A-C0BAD58B486F(a)microsoft.com... > Chip, thanks for the reply but :( > > below is the formula and my changes but it says too many arquements?? > excel > hights the first ",0". > > For clarity in cell ref in the hope I have done the correct changes: > > AN$9 is the first line/cell for the report results > f7:f198 is the range of value in AQ6 (both text)is to match > b7:b198 is where the value (i.e. PC01) to be return to an9 > > IF(ROW()-ROW(AN$9)<COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F$7:F$198,$AQ$6)))),0) > > I do thank you for your help as I am trying to understand the nore indepth > formulas > > UKMAN1 > > > "Chip Pearson" wrote: > >> Select the range of cells in which you want the results, say L18:L25. >> Assuming you have numeric values in D4:D11 and the corresponding >> values to return in E4:E11, enter the following array formula and >> press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the >> formula into an array of cells, rather than a single cell. >> >> =IF(ROW()-ROW(L$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))),0) >> >> Change the reference to L$18 to the first cell in the results range >> that contains the formula. Change the "b" to the value you want to >> look up in D4:D11. >> >> This is an array formula, so you MUST press CTRL SHIFT ENTER rather >> than just ENTER when you first enter the formula and whenever you edit >> it later. If you do this correctly, Excel will display the formula in >> the formula bar enclosed in curly braces { }. You don't type in the >> braces; Excel puts them there automatically. The formula will not work >> correctly if you do not enter it with CTRL SHIFT ENTER. See >> www.cpearson.com/Excel/ArrayFormulas.aspx for much more information >> about array formulas. >> >> >> >> Cordially, >> Chip Pearson >> Microsoft Most Valuable Professional, >> Excel, 1998 - 2010 >> Pearson Software Consulting, LLC >> www.cpearson.com >> >> >> >> >> On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN >> <UKMAN(a)discussions.microsoft.com> wrote: >> >> >=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) >> > >> >the above formula works in that it shows the first match. >> > >> >As there could be mulitpule records what do I need to do to so when I >> >copy >> >it down it will bring the next match or 0 if no further matches in the >> >table >> >it is searching? >> > >> >Many thanks >> > >> >UKMAN1 >> . >>
From: UKMAN on 16 Apr 2010 07:26 Mr T, many thanks and it worked perfectly. To all others many thanks as well for your help. Regards UKMAN1 "T. Valko" wrote: > Try this... > > Enter this formula in A1. This will return the count of records that meet > the criteria. > > =COUNTIF(F$7:F$198,AQ$6) > > Enter this array formula** in B1 and copy down until you get 0s.. This will > extract the records that meet the criteria. > > =IF(ROWS(B$1:B1)>A$1,0,INDEX(Proj_code,SMALL(IF(F$7:F$198=AQ$6,ROW(Proj_code)),ROWS(B$1:B1))-MIN(ROW(Proj_code))+1)) > > ** array formulas need to be entered using the key combination of > CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT > key then hit ENTER. > > -- > Biff > Microsoft Excel MVP > > > "UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message > news:58275365-98C0-4257-A757-B61FC0AD4A1B(a)microsoft.com... > > =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) > > > > the above formula works in that it shows the first match. > > > > As there could be mulitpule records what do I need to do to so when I copy > > it down it will bring the next match or 0 if no further matches in the > > table > > it is searching? > > > > Many thanks > > > > UKMAN1 > > > . >
From: UKMAN on 16 Apr 2010 07:28 Steve, many thanks for your help. UKMAN1 "Steve Dunn" wrote: > Too many arguments is referring to this part of your formula: > > IF(F$7:F$198, $AQ$6, B$7:B$198, 0) > > because an IF function only requires 3 arguments. I suspect you meant: > > IF(F$7:F$198=$AQ$6,B$7:B$198,0) > > > > "UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message > news:7BC729F1-52FA-4C18-959A-C0BAD58B486F(a)microsoft.com... > > Chip, thanks for the reply but :( > > > > below is the formula and my changes but it says too many arquements?? > > excel > > hights the first ",0". > > > > For clarity in cell ref in the hope I have done the correct changes: > > > > AN$9 is the first line/cell for the report results > > f7:f198 is the range of value in AQ6 (both text)is to match > > b7:b198 is where the value (i.e. PC01) to be return to an9 > > > > IF(ROW()-ROW(AN$9)<COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F$7:F$198,$AQ$6)))),0) > > > > I do thank you for your help as I am trying to understand the nore indepth > > formulas > > > > UKMAN1 > > > > > > "Chip Pearson" wrote: > > > >> Select the range of cells in which you want the results, say L18:L25. > >> Assuming you have numeric values in D4:D11 and the corresponding > >> values to return in E4:E11, enter the following array formula and > >> press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the > >> formula into an array of cells, rather than a single cell. > >> > >> =IF(ROW()-ROW(L$18)<COUNTIF(D4:D11,"b"),LARGE(IF(D4:D11="b",E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D4:D11,"b")))),0) > >> > >> Change the reference to L$18 to the first cell in the results range > >> that contains the formula. Change the "b" to the value you want to > >> look up in D4:D11. > >> > >> This is an array formula, so you MUST press CTRL SHIFT ENTER rather > >> than just ENTER when you first enter the formula and whenever you edit > >> it later. If you do this correctly, Excel will display the formula in > >> the formula bar enclosed in curly braces { }. You don't type in the > >> braces; Excel puts them there automatically. The formula will not work > >> correctly if you do not enter it with CTRL SHIFT ENTER. See > >> www.cpearson.com/Excel/ArrayFormulas.aspx for much more information > >> about array formulas. > >> > >> > >> > >> Cordially, > >> Chip Pearson > >> Microsoft Most Valuable Professional, > >> Excel, 1998 - 2010 > >> Pearson Software Consulting, LLC > >> www.cpearson.com > >> > >> > >> > >> > >> On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN > >> <UKMAN(a)discussions.microsoft.com> wrote: > >> > >> >=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) > >> > > >> >the above formula works in that it shows the first match. > >> > > >> >As there could be mulitpule records what do I need to do to so when I > >> >copy > >> >it down it will bring the next match or 0 if no further matches in the > >> >table > >> >it is searching? > >> > > >> >Many thanks > >> > > >> >UKMAN1 > >> . > >> >
From: UKMAN on 16 Apr 2010 07:28 Mike, many thanks for your help. UKMAN1 "Mike H" wrote: > Hi, > > Try this ARRAY formula. ARRAY enter it and it will return the first match, > drag down for the second etc. It will return an error if there isn't a second > match so you could wrap the whole thing =isserror(formula etc > > =OFFSET(IF(ROWS(B$7:B7)<=COUNTIF(Proj_code,$E$1),INDEX($F$7:$F$198,SMALL(IF(Proj_code=$AQ$6,ROW(Proj_code)-ROW($E$1)+1),ROWS(B$7:B7))),""),-6,0) > > This is an array formula which must be entered by pressing CTRL+Shift+Enter > and not just Enter. If you do it correctly then Excel will put curly brackets > around the formula {}. You can't type these yourself. If you edit the formula > you must enter it again with CTRL+Shift+Enter. > > > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "UKMAN" wrote: > > > =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) > > > > the above formula works in that it shows the first match. > > > > As there could be mulitpule records what do I need to do to so when I copy > > it down it will bring the next match or 0 if no further matches in the table > > it is searching? > > > > Many thanks > > > > UKMAN1
From: T. Valko on 16 Apr 2010 10:36
You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message news:E1C30917-3CAB-4935-879E-3FC0555D09D5(a)microsoft.com... > Mr T, > > many thanks and it worked perfectly. > > To all others many thanks as well for your help. > > Regards > > UKMAN1 > > "T. Valko" wrote: > >> Try this... >> >> Enter this formula in A1. This will return the count of records that meet >> the criteria. >> >> =COUNTIF(F$7:F$198,AQ$6) >> >> Enter this array formula** in B1 and copy down until you get 0s.. This >> will >> extract the records that meet the criteria. >> >> =IF(ROWS(B$1:B1)>A$1,0,INDEX(Proj_code,SMALL(IF(F$7:F$198=AQ$6,ROW(Proj_code)),ROWS(B$1:B1))-MIN(ROW(Proj_code))+1)) >> >> ** array formulas need to be entered using the key combination of >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the >> SHIFT >> key then hit ENTER. >> >> -- >> Biff >> Microsoft Excel MVP >> >> >> "UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message >> news:58275365-98C0-4257-A757-B61FC0AD4A1B(a)microsoft.com... >> > =INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0)) >> > >> > the above formula works in that it shows the first match. >> > >> > As there could be mulitpule records what do I need to do to so when I >> > copy >> > it down it will bring the next match or 0 if no further matches in the >> > table >> > it is searching? >> > >> > Many thanks >> > >> > UKMAN1 >> >> >> . >> |