stumped by my boss...

[robd11]

Ive been lurking on these boards since I started working and a former boss of mine steered me to the site. you don't know it but many of you have been incredibly helpful over the last 6+ years, so thank you.

boss of mine decided to start a new monthly excel challenge and while I have one way of solving the problem, it is not the more "elegant, simplified" way he does it. it's absolutely killing me and he won't share until someone figures it out...problem is, google and this site are generally how I solve these problems and I can't find exactly what Im looking for...so here I am

its a simple data set (table below showing small example), and the idea is to write a formula in column j that will identify the 4 digit project numbers (B1-G1) that each employee spent >= 50% of their time on. Column I has the answers. Before anyone suggests it, I've already determined that I can CONCATENATE multiple IF statements, but he insists there is an easier way.

let er rip

employee	1234	1235	1288	1646	1586	1975		answers	formula
a		50%	50%					1235,1288
b		100%						1235
c				100%				1646
d		70%			30%			1235
e						100%		1975
f	40%		60%					1288
g				50%	50%			1646,1586

<tbody>
</tbody>

forgive me for not attaching the file, work computer restrictions...

 

[Rick Rothstein]

You were looking for a formula solution, correct? Here is an array-entered** formula that appears to work (assumes Employee names are located in Column A and that no code in Row 1 starts with 0)...

=SUBSTITUTE(MAX(B$1:G$1*(B2:G2>=0.5))&", "&LARGE(B$1:G$1*(B2:G2>=0.5),2),", 0","")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

This might be a better way to write the above formula...

=MAX(B$1:G$1*(B2:G2>=0.5))&IF(COUNTIF(B2:G2,0.5)>1,", "&LARGE(B$1:G$1*(B2:G2>=0.5),2),"")