Generating a list from a dyamic array

[Patrick020]

Hi everyone <o></o>
<o> </o>
I would like to generate a list which calculates the largestshortfalls out of a variety of projects. Shortfall is defined as Revenue minusCost. <o></o>
<o> </o>
I would like the formulas to allow you to select a specificfunction from which to assess their projects (in cell G1 below). The array thereforeneeds to dynamic and change depending on which function you choose in G1 whichis what I am struggling with. <o></o>
<o> </o>
I have put an example below: <o></o>
<o> </o>
Ideally I would like a formula to drag down in F4 and in G4.<o></o>
<o> </o>

	A<o></o>	B<o></o>	C<o></o>	D<o></o>	E<o></o>	F<o></o>	G<o></o>
1<o></o>	Function<o></o>	Project<o></o>	Cost<o></o>	Revenue <o></o>		Selection: <o></o>	Ops <o></o>
2<o></o>	Sales <o></o>	Project 1<o></o>	50<o></o>	41<o></o>		<o> </o>	<o> </o>
3<o></o>	Sales <o></o>	Project 2<o></o>	21<o></o>	5<o></o>		Deficit <o></o>	Project<o></o>
4<o></o>	Sales <o></o>	Project 3<o></o>	6<o></o>	44<o></o>		-14<o></o>	Option 2 <o></o>
5<o></o>	Sales <o></o>	Project 4<o></o>	66<o></o>	19<o></o>		-14<o></o>	Option 3<o></o>
6<o></o>	Sales <o></o>	Project 5<o></o>	36<o></o>	25<o></o>		-4<o></o>	Option 1<o></o>
7<o></o>	HR <o></o>	Scheme 1<o></o>	80<o></o>	66<o></o>		9<o></o>	Option 4 <o></o>
8<o></o>	HR <o></o>	Scheme 2<o></o>	69<o></o>	17<o></o>		<o> </o>	<o> </o>
9<o></o>	HR <o></o>	Scheme 3<o></o>	27<o></o>	57<o></o>
10<o></o>	Ops<o></o>	Option 1<o></o>	77<o></o>	73<o></o>
11<o></o>	Ops<o></o>	Option 2<o></o>	68<o></o>	54<o></o>
12<o></o>	Ops<o></o>	Option 3<o></o>	19<o></o>	5<o></o>
13<o></o>	Ops<o></o>	Option 4<o></o>	54<o></o>	63<o></o>
14<o></o>	Ops<o></o>	Option 5<o></o>	39<o></o>	72<o></o>
15<o></o>	Ops<o></o>	Option 6<o></o>	39<o></o>	53<o></o>

<tbody> </tbody>

<o> </o>
Hopefully that’s clear but let me know if not! <o></o>
<o> </o>
Thank you<o></o>
Patrick

 

[Oscar Cronquist]

Book1
	A	B	C	D	E	F	G	H
1	Function	Project	Cost	Revenue		Selection:	Ops
2	Sales	Project 1	50	41
3	Sales	Project 2	21	5		Deficit	Project
4	Sales	Project 3	6	44		14	Option 6
5	Sales	Project 4	66	19		33	Option 5
6	Sales	Project 5	36	25		9	Option 4
7	HR	Scheme 1	80	66		-14	Option 3
8	HR	Scheme 2	69	17		-14	Option 2
9	HR	Scheme 3	27	57		-4	Option 1
10	Ops	Option 1	77	73
11	Ops	Option 2	68	54
12	Ops	Option 3	19	5
13	Ops	Option 4	54	63
14	Ops	Option 5	39	72
15	Ops	Option 6	39	53
16
Sheet

Formula in cell G4:
=LOOKUP(2, 1/((COUNTIF($G$3:G3, $B$2:$B$15)=0)*($G$1=$A$2:$A$15)), $B$2:$B$15)

Formula in cell F4:
=SUMIFS($D$2:$D$15,$A$2:$A$15,$G$1,$B$2:$B$15,G4)-SUMIFS($C$2:$C$15,$A$2:$A$15,$G$1,$B$2:$B$15,G4)

Drag to cells below.

 

[Patrick020]

Hi Oscar

Apologies I probably should have made it clearer that the list in F4:F7 needs to identify the largest shortfalls first (i.e. this is the ranking aspect). To provide some context on this request - the actual spreadsheet has approx. 100 projects for some functions, so I only want to pull the top ten shortfalls.

The table I have in the OP lists the correct answer for Ops - it should list Option 2 and Option 3 first as they are the largest shortfalls at -14.

I have added another example below this time with Sales to make it clear:

	A<o></o>	B<o></o>	C<o></o>	D<o></o>	E<o></o>	F<o></o>	G<o></o>
1<o></o>	Function<o></o>	Project<o></o>	Cost<o></o>	Revenue <o></o>		Selection: <o></o>	Sales<o></o>
2<o></o>	Sales <o></o>	Project 1<o></o>	50<o></o>	41<o></o>		<o></o>	<o></o>
3<o></o>	Sales <o></o>	Project 2<o></o>	21<o></o>	5<o></o>		Deficit <o></o>	Project<o></o>
4<o></o>	Sales <o></o>	Project 3<o></o>	6<o></o>	44<o></o>		-47<o></o>	Project 4 <o></o>
5<o></o>	Sales <o></o>	Project 4<o></o>	66<o></o>	19<o></o>		-16<o></o>	Project 2<o></o>
6<o></o>	Sales <o></o>	Project 5<o></o>	36<o></o>	25<o></o>		-11<o></o>	Project 5<o></o>
7<o></o>	HR <o></o>	Scheme 1<o></o>	80<o></o>	66<o></o>		-9<o></o>	Project 1 <o></o>
8<o></o>	HR <o></o>	Scheme 2<o></o>	69<o></o>	17<o></o>		38<o></o>	Project 3 <o></o>
9<o></o>	HR <o></o>	Scheme 3<o></o>	27<o></o>	57<o></o>
10<o></o>	Ops<o></o>	Option 1<o></o>	77<o></o>	73<o></o>
11<o></o>	Ops<o></o>	Option 2<o></o>	68<o></o>	54<o></o>
12<o></o>	Ops<o></o>	Option 3<o></o>	19<o></o>	5<o></o>
13<o></o>	Ops<o></o>	Option 4<o></o>	54<o></o>	63<o></o>
14<o></o>	Ops<o></o>	Option 5<o></o>	39<o></o>	72<o></o>
15<o></o>	Ops<o></o>	Option 6<o></o>	39<o></o>	53<o></o>

<tbody>
</tbody>

 

[Patrick020]

To make this easier we can ignore the deficit aspect – it’s the dynamic array part that I am really concerned about. <o></o>
<o></o>
I have therefore simplified the example below: <o></o>
<o></o>
The simplified objective is to create a list which calculates the largest values based on the selection in G2. A formula is required in in F4 (to identify the values) and in G4 (to identify the project name). <o></o>
<o></o>

	A<o></o>	B<o></o>	C<o></o>	D<o></o>	E<o></o>	F<o></o>	G<o></o>
1<o></o>	Function<o></o>	Project<o></o>	Value<o></o>	<o></o>		Selection: <o></o>	Sales<o></o>
2<o></o>	Sales <o></o>	Project 1<o></o>	50<o></o>	<o></o>		<o></o>	<o></o>
3<o></o>	Sales <o></o>	Project 2<o></o>	21<o></o>	<o></o>		Value <o></o>	Project<o></o>
4<o></o>	Sales <o></o>	Project 3<o></o>	6<o></o>	<o></o>		66<o></o>	Project 4 <o></o>
5<o></o>	Sales <o></o>	Project 4<o></o>	66<o></o>	<o></o>		50<o></o>	Project 1<o></o>
6<o></o>	Sales <o></o>	Project 5<o></o>	36<o></o>	<o></o>		36<o></o>	Project 5<o></o>
7<o></o>	HR <o></o>	Scheme 1<o></o>	80<o></o>	<o></o>		<o></o>	<o></o>
8<o></o>	HR <o></o>	Scheme 2<o></o>	69<o></o>	<o></o>		<o></o>	<o></o>
9<o></o>	HR <o></o>	Scheme 3<o></o>	27<o></o>	<o></o>		<o></o>
10<o></o>	Ops<o></o>	Option 1<o></o>	77<o></o>	<o></o>
11<o></o>	Ops<o></o>	Option 2<o></o>	68<o></o>	<o></o>
12<o></o>	Ops<o></o>	Option 3<o></o>	19<o></o>	<o></o>
13<o></o>	Ops<o></o>	Option 4<o></o>	54<o></o>	<o></o>
14<o></o>	Ops<o></o>	Option 5<o></o>	39<o></o>	<o></o>
15<o></o>	Ops<o></o>	Option 6<o></o>	39<o></o>	<o></o>

<tbody>
</tbody>

 

[Oscar Cronquist]

Array formula in cell F4:
=LARGE(($A$2:$A$15=$G$1)*$C$2:$C$15,ROWS($A$1:A1))

Array formula in cell G4:
=INDEX($B$2:$B$15,MIN(IF(($A$2:$A$15=$G$1)*($C$2:$C$15=F4)*(COUNTIF($G$3:G3,$B$2:$B$15)=0),MATCH(ROW($A$2:$A$15),ROW($A$2:$A$15)),"")))