Lookups within Array Formulas

[akcramblet]

I'm pretty good with excel, but this is tricky...

I have a table of bid results for multiple projects my company has bid.

Outside of the table, I need to look at a given company's performance, like the total backlog (or rank 1 bids):

{=SUM(([Company]=F2)*([Rank]=1)*([Bid]))} = $691,469

Not too bad.

I'd also like to see the total "tabled" bid dollars for a given company. Tabled dollars is the difference between the first and second ranked bidders on a single project. So for a given company, I need to add up the total rank 1 bids, and subtract that number from the corresponding rank 2 bids. This is what I've tried, but it doesn't seem to work (The problem is in the second half, which is really all I need):

{=SUM(([Company]=F2)*([Rank]=1)*([Bid]))-SUM((([Project]=IF([Company] =F2,IF([Rank]=1,[Project])))*([Rank]=2)*([Bid Amount])))}

I've never encountered this type of puzzle before, but can't afford the time to figure it out.

Any help is appreciated. Thanks.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Project	Company	Bid	Rank		Company	Backlog	Tabled $
2	AAA	Bill	$1,583,777	1		Silas	$691,469	????
3	AAA	Bob	$1,644,807	2
4	AAA	John	$1,645,761	3		???? =
5	AAA	Silas	$1,704,000	4		$57,872
6	BBB	Silas	$210,000	1
7	BBB	Bob	$215,000	2
8	BBB	John	$223,500	3
9	CCC	Silas	$481,469	1
10	CCC	Bill	$534,341	2
11	EEE	John	$1,365,449	1
12	EEE	Bill	$1,367,757	2
13	EEE	Silas	$1,393,100	3
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[pgc01]

Akcramblet, I'm glad it helped, thanks for the feedback.

Let me know if I can return the favor.

Yes you can. Now and again, when you have the time (and the patience), browse the board's questions and, if you see a problem that you know how to solve, help out.

Cheers.

 

[pgc01]

Mike

I though of another option. I did not choose it because it's volatile, and I try to avoid them, but it's simpler and should be considered.

=SUM(IF(B2:B13=F2,IF(D2:D13=1,N(OFFSET(C2,MATCH(A2:A13&"|2",A2:A13&"|"&D2:D13,0)-1,0))-C2:C13)))

.. and still another one, a udf.

 

[mgirvin]

pgc01,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
More than magnificnet!<o></o>
<o></o>
This is great:<o></o>
<o></o>
=SUM(IF(B2:B13=F2,IF(D2:D13=1,N(OFFSET(C2,MATCH(A2:A13&"|2",A2:A13&"|"&D2:D13,0)-1,0))-C2:C13)))<o></o>
<o></o>
This is the first time I have seen N used to get values from OFFSET. I had seen SUBTOTAL to do this before. Very nice! I would have done something like:

=SUM(IF(B2:B13=F2,IF(D2:D13=1,SUBTOTAL(9,OFFSET(C2,MATCH(A2:A13&"|2",A2:A13&"|"&D2:D13,0)-1,0))-C2:C13)))
<o></o>
<o></o>
What do you think of this:<o></o>
<o></o>
=SUMPRODUCT(--(B2:B13=F2),--(D2:D13=1),N(OFFSET(C1,MATCH(A2:A13&2,A2:A13&D2:D13,0),))-C2:C13)<o></o>
<o></o>
A couple of questions:<o></o>
<o></o>
1) Is there a reason that you used SUM and IF instead of SUMPRODUCT? What advantage might there be?
2) Is it necessary to concatenate an extra character such as "|". I used to do that and then slowly over the years I have gotten lazy and stopped doing it and then because I have not run into problems I just started making it my norm. Is there a situation where the extra character would be 100% necessary?<o></o>
<o></o>
It is fantastic how over the years I have really learned good tips from you, pgc01!!!<o></o>
<o></o>

 

[Matty]

Hi All,

I've been watching this thread with interest. I hadn't realised we could use SUMIFS as demonstrated by pgc01, which is great to know.

In terms of your questions, Mike, I'm sure pgc01 will articulate things better than I ever could, but I think...

1) Is there a reason that you used SUM and IF instead of SUMPRODUCT? What advantage might there be?

...simply comes down to personal preference, though there might be something I don't know.

And in terms of...

2) Is it necessary to concatenate an extra character such as "|". I used to do that and then slowly over the years I have gotten lazy and stopped doing it and then because I have not run into problems I just started making it my norm. Is there a situation where the extra character would be 100% necessary?

I always delimit when concatenating for the reason illustrated below:

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:50px;"><col style="width:50px;"><col style="width:47px;"><col style="width:20px;"><col style="width:50px;"><col style="width:50px;"><col style="width:179px;"><col style="width:163px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Code A</td><td>Code B</td><td>Data C</td><td>
</td><td>Code A</td><td>Code B</td><td>Value Returned (no delimiter)</td><td>Value Returned (delimited)</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">123</td><td style="text-align:right; ">456</td><td style="text-align:right; ">5</td><td>
</td><td style="text-align:right; ">1234</td><td style="text-align:right; ">56</td><td style="text-align:right; ">5</td><td style="text-align:right; ">10</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">1234</td><td style="text-align:right; ">56</td><td style="text-align:right; ">10</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>G2</td><td>{=INDEX(C2:C3,MATCH(E2&F2,A2:A3&B2:B3,0))}</td></tr><tr><td>H2</td><td>{=INDEX(C2:C3,MATCH(E2&"|"&F2,A2:A3&"|"&B2:B3,0))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</td></tr></tbody></table>
Situations such as this are probably few and far between, but better to be safe than sorry!

Matty

 

[mgirvin]

Got it, Matty! That makes perfect sense that to join the numbers may cause problems. That means I am back to using an extra character when I join things, because I do do it very often and it is better to be safe than sorry.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[pgc01]

Hi Mike. Thanks, I also think that it's amazing how much we learn from each other in this board.

For the question 1:

There is no special reason. I just translated directly what I would do if I were to perform the calculations manually, it would be something like "loop through the records and for each one if the name of the company is equal to the value in F2 then if the Rank is equal to 2 then ...". The "Sum(If(" formula is very close to the natural language.

Your SumProduct() formula seems perfect and has the advantage of not needing to be confirmed with CSE. Sumproduct() is also slightly faster than the equivalent array formula, so there's another advantage.

For the question 2:

Matty posted a nice example that shows why it's important in some cases to use a separator when you concatenate.

Cheers!

 

[mgirvin]

Cheers to all at the amazing Message Board! Thanks!