# if..small..rows - logic issue

1. ## if..small..rows - logic issue

hello

here my formula:

{=SMALL(IF('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$77,IF('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$11,IF('SE 2011 Status'!\$F\$14:\$F\$304=\$D\$12,'SE 2011 Status'!\$W\$14:\$W\$304,""),""),""),K11)}

basically, im searching by three criteria
1) revenue bucket (\$0-\$10.99; \$11-\$24.99 etc)
2) tenure
3) region

issue:
it should list 4 ppl...but bc they are in the first bucket \$0-\$10.99 and all four are \$0, it is only using the first person's name that appears and show it four times

\$0 John Doe
\$0 John Doe
\$0 John Doe
\$0 John Doe

how can i make it so it only shows the person's name once? pls help!! ive been stuck at this for a day now :S

pls see attached file *i have highlighted row in red**

i was struggling to attach the file on here (not a big fan of the new change to the site)...so i have it on a shared drive..pls help!!!

https://skydrive.live.com/redir.aspx...108&parid=root

2. ## Re: if..small..rows - logic issue

Try rearranging formula as:

``Please Login or Register  to view this content.``
Confirmed with CTRL+SHIFT+ENTER

3. ## Re: if..small..rows - logic issue

hello NBVC

thnk u so much.

in column N, my formula has gotten pretty huge

{=IF(\$D\$12=\$D\$18,INDEX('SE 2011 Status'!\$C\$14:\$C\$304,SMALL(IF('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$77,IF('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$11,IF('SE 2011 Status'!\$G\$14:\$G\$304=\$D\$12,ROW('SE 2011 Status'!\$C\$14:\$C\$304)-ROW('SE 2011 Status'!\$C\$14)+1))),K11)),INDEX('SE 2011 Status'!\$C\$14:\$C\$304,SMALL(IF('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$77,IF('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$11,IF('SE 2011 Status'!\$F\$14:\$F\$304=\$D\$12,ROW('SE 2011 Status'!\$C\$14:\$C\$304)-ROW('SE 2011 Status'!\$C\$14)+1))),K11)))}

it works great...but i.e. if there are 4 entries within my criteria....the remaining 3 ranks show #NUM error.

when i try to place if(iserror....it says the formula is too long...ur thoughts/suggestions thx u sir!

4. ## Re: if..small..rows - logic issue

Try:

``Please Login or Register  to view this content.``
You can replace the nested IFs with arrays multiplied by each other (that acts like AND function)... then the error trap used is LOOKUP(REPT("z",255),CHOOSE({1,2},"" which eliminates need for you to repeat the whole statement twice.

5. ## Re: if..small..rows - logic issue

hello nbvc

one final request or modification

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",IF(\$D\$23=\$D\$18,INDEX('SE 2011 Status'!\$C\$14:\$C\$304,SMALL(IF(('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$80)*('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$22)*('SE 2011 Status'!\$G\$14:\$G\$304=\$D\$23),ROW('SE 2011 Status'!\$C\$14:\$C\$304)-ROW('SE 2011 Status'!\$C\$14)+1),K22)),INDEX('SE 2011 Status'!\$C\$14:\$C\$304,SMALL(IF(('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$80)*('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$22)*('SE 2011 Status'!\$F\$14:\$F\$304=\$D\$23),ROW('SE 2011 Status'!\$C\$14:\$C\$304)-ROW('SE 2011 Status'!\$C\$14)+1),K22)))))

that works great, however...how can i make it lookoff the value in column L?

basically...right now it's dependent on tenure, region and revenue criteria...how it does not match the entry that is made in column L for i.e. \$35?

also, i tried to place this in column L...but cant seem to work - too many arguments?

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",IF(\$D\$12<>\$D\$18,INDEX('SE 2011 Status'!\$W\$14:\$W\$304,SMALL(IF('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$77,IF('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$11,IF('SE 2011 Status'!\$F\$14:\$F\$304=\$D\$12,ROW('SE 2011 Status'!\$W\$14:\$W\$304)-ROW('SE 2011 Status'!\$W\$14)+1))),K11)),INDEX('SE 2011 Status'!\$W\$14:\$W\$304,SMALL(IF('SE 2011 Status'!\$S\$14:\$S\$304='Raw data 1'!\$E\$77,IF('SE 2011 Status'!\$O\$14:\$O\$304=Summary!\$D\$11,IF('SE 2011 Status'!\$G\$14:\$G\$304=\$D\$12,ROW('SE 2011 Status'!\$W\$14:\$W\$304)-ROW('SE 2011 Status'!\$W\$14)+1))),K11))))

6. ## Re: if..small..rows - logic issue

As I mentioned, I not only added the LOOKUP(.. CHOOSE(...))), but I replaced your nested IF's with array multiplcations

``Please Login or Register  to view this content.``
I don't understand the request about "looking off of L"?

You want to add another condition? If so what exactly.

7. ## Re: if..small..rows - logic issue

hello NBVC

i really appreciate the prompt response/help!!!

i have attached a *NEW* sample .....i have highlighted in red...basically...the calculation is fine....however i would like to arrange the column L from smallest to largest....ur thoughts? thx u and this is it

thxs a million again

https://skydrive.live.com/redir.aspx...Pt-CEvb95ppQhg

8. ## Re: if..small..rows - logic issue

In L11, try:

``Please Login or Register  to view this content.``
in N11:

``Please Login or Register  to view this content.``
both need CSE confirme and copy down.

Adjust other formulas int the other columns similar to the N11 formula.

9. ## Re: if..small..rows - logic issue

for some odd reason, in column N, for criteria tenure (13-24) region North american, and zone \$0-\$10.99...the names dont show anything? strange

10. ## Re: if..small..rows - logic issue

A couple of things on my part... I assumed you didn't want names for \$0 and I didn't add the G range for when D12=D18...

so...

``Please Login or Register  to view this content.``
CSE confirmed

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1