• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Incrementing Formula using ROW, INDEX, SMALL and IF - Does not work! Why?

C

chloec

Member
Hi There, I am not sure why the formula below not working for me. Can you please help me understand if anything should be fixed?


=IF(ROWS(F$68:F68)<=G51,INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$5,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(F$68:F68))),"")

I know that the formula is entered as an ARRAY {}.

The array formula will be posted in cell F68 and copied down.

Cell D5 = Jenna and G51=4

Small formula is used as an incrementer(and it seems to work)

Basically I want the formula to return in cells F68, F69, F70, F71 all the comments that match to the "Jenna" index (data from cell B2, B5,B9,B10). I keep getting #N/A

[pre]
Code: 
A1 Player	B1 Comment
A2 Jenna	B2 Jenna - 11/28/11 Game Score 1
A3 BLX	        B3 CA not relevant
A4 BLX	        B4 CA not relevant
A5 Jenna	B5 CA not relevant
A6 FOJO	        B6 Fojo - 05/26/11 Game Score 14.4
A7 FOJO	        B7 Fojo - 05/26/11 Game Score 14.4
A8 FOJO	        B8 Fojo - 05/26/11 Game Score 14.4
A9 Jenna	B9 Jenna - 11/28/11 Game Score 22
A10 Jenna	B10 Jenna - 11/28/11 Game Score 22
[/pre]
 
Try this formula. It is an Array so Enter with Ctrl+shift+enter


=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$D$5,ROW($A$1:$A$10)),ROW(A1)))


If you want to hide the errors after all the comments are found. This works.


=IF(ISERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$D$5,ROW($A$1:$A$10)),ROW(1:1)))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$D$5,ROW($A$1:$A$10)),ROW(1:1))))
 
I think you just needed to make the G51 reference absolute. I've taken the liberty of simplifying the latter portion of the formula.

=IF(ROWS(F$68:F68)<=$G$51,INDEX(B:B,SMALL(IF($A$2:$A$10=$D$5,ROW($A$2:$A$10)),ROWS(F$68:F68))),"")
 
Thank you so much! I actually figured out why my formula broke - not sure if you have any solutions. Some of my datapoints in col A and B may have (as an actual piece of data the characters) "#N/A" - the minute that is a data point, the formula breaks. Do you know why this occurs and how to easily fix it?

Thanks again, lovely work!


I don't know if you can figure this out without values, but this is the formula that I am using that results in "#N/A" and that's OK.


=IF(AND(ISNUMBER(SEARCH("- DNU",W3))=FALSE,F3>=Temp_CPS!$G$40,F3<=SUM(Temp_CPS!$R$40)*DAY(DATE(YEAR(F3),MONTH(F3)+1,1)-1)),CONCATENATE(W3," - ",TEXT(F3,"MM/DD/YY")," ",CACount!G3," ",CACount!H3," vs. ",I3," limit. Score =",L3),"CA not relevant")
 
Try my second formula Chloec.


Here it is again.


=IF(ISERROR(INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$D$5,ROW($A$1:$A$10)),ROW(1:1)))),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10=$D$5,ROW($A$1:$A$10)),ROW(1:1))))
 
Hmm. It appears just typing the text of one of XL's error's will cause it to be treated as an error. As such, it's causing problems in the IF function. We can work around it by adding a logic check to check for the #N/A error.

=IF(ROWS(F$68:F68)<=$G$51,INDEX(B:B,SMALL(IF(NOT(ISNA($A$2:$A$10)),IF($A$2:$A$10=$D$5,ROW($A$2:$A$10))),ROWS(F$68:F68))),"")


For the formula you posted, one of the arguments in the formula function must be causing an error (not sure which, could be anyone of them). Perhaps using the evaluate formula function will shed some light? I did take the liberty of condensing it a little:

=IF(AND(ISNA(SEARCH("- DNU",W3)),F3>=Temp_CPS!$G$40,F3<=Temp_CPS!$R$40*DAY(EOMONTH(F3,0))),W3&" - "&TEXT(F3,"MM/DD/YY")&" "&CACount!G3&" "&CACount!H3&" vs. "&I3&" limit. Score ="&L3,"CA not relevant")
 
Thank you very much! I am thrilled that I figured out a solutin for my first question. And thank you for condensing. However, I am still working on removing the "#N/A" as a result. It's a shame because I don't mind it being there.


Here's another example:

=IF(AND(NOT(EXACT(E1,E2)),VLOOKUP(A2,ScorecardDirections!B:C,2,FALSE)="Yes",P2<>"0"),A2,CONCATENATE(A2," - DNU"))


The VLOOKUP is the key to the "#N/A" appearing. I have a list of items that are on a VLOOKUP list, if they aren't on the list, I don't care about them (hence, I didn't worry about "#N/A" showing up in my formula, because I'd ignore the data. When I started using this formulas in my incrementing formula, the "#N/A" has been messing things up). Now I wnat to get rid of "#N/A" using the formula. I know EXACTLY why it's there...


Here's the values for the Vlookup table:

[pre]
Code: 
Type    Player	Tracked
12	Bubba	Yes
12	Bbleat	Yes
12	Bbubba2	Yes
12	Bracca	Yes
12	Brocco	Yes
12	CFF	Yes
12	CFF2	Yes
12	Hono	Yes
12	Horo	Yes
12	Cff3	Yes
12	Lee	Yes
12	Primo	Yes
12	Primo2	Yes
12	Simmo	Yes
12	Theo	Yes
12	Teecee	Yes
12	Weebo	Yes
12	XXXX	Yes
[/pre]
For example, because "Chloe" does NOT appear as a player - I would like the "=IF(AND(NOT" formula to write something else, like "Does not Matter". Also, I still would like to add "DNU" to the name if it shows up on the Player list AND cells E1+E2 are exact.


Thanks again! Everyone is so great - Luke, Montrey - thank you!
 
How's this?

=IF(COUNTIF(ScorecardDirections!B:B,A2)=0,"Does not Matter",IF(AND(NOT(EXACT(E1,E2)),VLOOKUP(A2,ScorecardDirections!B:C,2,FALSE)="Yes",P2<>"0"),A2,A2&" - DNU"))


One word of caution, "0" <> 0. When you are comparing P2, does it contain a number, or a text string? If a number, you should just have:

P2<>0

If it really is a text string (odd, but possible), then it is okay as is.
 
That works great. I guess for P2, what I was going for is this:

- The default cell entry is 0. If it contains anything other than 0, then invoke "

- DNU" Thoughts? Thank you, Luke!
 
So, it's DNU if "not" 0?

=IF(COUNTIF(ScorecardDirections!B:B,A2)=0,"Does not Matter",IF(AND(NOT(EXACT(E1,E2)),VLOOKUP(A2,ScorecardDirections!B:C,2,FALSE)="Yes",P2=0),A2,A2&" - DNU"))
 
You must log in or register to reply here.
Back
Top