formula or function or what

[sab_wow]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]dept[/TD]
[TD]week 1[/TD]
[TD]week 2[/TD]
[TD]week 3[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]a[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]james[/TD]
[TD]a[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]james[/TD]
[TD]b[/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]queen[/TD]
[TD]b[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


imagine a much more larger data set with more names, weeks.
how do you identify the names that worked more than 30 hrs in any week?

 

[sheetspread]

Add a helper column with a max formula, then filter by >30

 

[sab_wow]

when certain names appear multiple times e.g james. how do you identify the names that appear multiple times as they work in the 2 departments and work over 30 hrs ?

 

[Finalfight40]

Do you mean James works in Dept A and B and is the same person?

 

[SpillerBD]

You need to un-pivot your data.
Then with a proper data-set you can get the sums you wish to evaluate in a Pivot Table and Filter by greater than...

 

[Finalfight40]

Do you mean you want something like this?

This is assuming James in department A and B are the same person?

[TABLE="width: 661"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]dept[/TD]
[TD] New Column[/TD]
[TD]week 1[/TD]
[TD]week 2[/TD]
[TD]week 3[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]a[/TD]
[TD] [/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]james[/TD]
[TD]a[/TD]
[TD]Worked more than 30 Hours[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]james[/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]queen[/TD]
[TD]b[/TD]
[TD] [/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

You could use Conditional Formatting. Given the layout from post # 1, A1:E5, select A2:A5, click Conditional Formatting > New Rule > Use a formula > and enter:

=MAX(MMULT(TRANSPOSE(ROW($A$2:$A$5)^0),$C$2:$E$5*($A$2:$A$5=A2)))>30

click Format... and choose a fill color. You'll need to change the selection range as well as the ranges in the formula to match your sheet. You can extend the ranges both vertically and horizontally past your table to allow room for expansion. Or you can insert a row (or column) at the end, and the formula will adapt to include it.

I'm not sure how well this will scale. With larger ranges, it might start to bog down your sheet.

 

[sab_wow]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]dept[/TD]
[TD]class[/TD]
[TD]week 1[/TD]
[TD]week 2[/TD]
[TD]week 3[/TD]
[/TR]
[TR]
[TD]james[/TD]
[TD]a[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]james[/TD]
[TD]b[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]-[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]a[/TD]
[TD]2[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]queen[/TD]
[TD]b[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]kyrie[/TD]
[TD]a[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]kyrie[/TD]
[TD]b[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]klay[/TD]
[TD]b[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]






The james is the same person. Multiple names appear in the data that are the same person e.g james, kyrie

identify those in class 1 that worked >30hrs in any week
identify those in class 2 that worked >40hrs in any week

so how do you identify those that worked >30 hrs and >40 hrs because taking only the first james" for example is not >30 but when you combine the 2 departments, its >30
?

 

[sheetspread]

A simple way is to add the max helper column, set a pivot table with max as the value field, and filter by >30 or whatever you like:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	name	dept	week 1	week 2	week 3	Max		Row Labels	Max of Max
2	john	a	10	10	25	25		james	90
3	james	a	30	30	30	30		queen	30
4	james	b	20		90	90
5	queen	b	30	30	30	30
Sheet12

Cell Formulas
Range	Formula
F2	=MAX(C2:E2)