Can we write a nested IF statement for the Large function?

U

UrbanJungleFashion

New Member
Joined
Oct 14, 2014
Messages
17
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]A B C
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Location[/TD]
[TD="width: 64"]Region[/TD]
[TD="width: 64"] Var[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]484[/TD]
[TD="class: xl69, align: right"]-38%[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]521[/TD]
[TD="class: xl69, align: right"]-46%[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]803[/TD]
[TD="class: xl69, align: right"]-12%[/TD]
[/TR]
[TR]
[TD]0004[/TD]
[TD]484[/TD]
[TD="class: xl69, align: right"]-25%[/TD]
[/TR]
[TR]
[TD]0005[/TD]
[TD]484[/TD]
[TD="class: xl69, align: right"]-74%[/TD]
[/TR]
[TR]
[TD]0006[/TD]
[TD]521[/TD]
[TD="class: xl69, align: right"]-36%[/TD]
[/TR]
[TR]
[TD]0007[/TD]
[TD]803[/TD]
[TD="class: xl69, align: right"]-45%[/TD]
[/TR]
[TR]
[TD]0008[/TD]
[TD]803[/TD]
[TD="class: xl69, align: right"]-47%[/TD]
[/TR]
[TR]
[TD]0009[/TD]
[TD]521[/TD]
[TD="class: xl69, align: right"]-81%[/TD]
[/TR]
[TR]
[TD]0010[/TD]
[TD]484[/TD]
[TD="class: xl69, align: right"]-74%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]484[/TD]
[TD="class: xl69, align: right"]-25%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]521[/TD]
[TD="class: xl69, align: right"]-36%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]803[/TD]
[TD="class: xl69, align: right"]-12%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"] [/TD]
[/TR]
</tbody>[/TABLE]


I have been working on this issue all weekend. I want to find the location with the best variance, even if the number is negative. I am tried to write a Large formula with a nested if statement. For example, if Cell C15=484, then what location based in region 484 has the highest variances. The max function does not work because it only addresses positive integers. I tried the large function but I have had no success in retrieving the correct answer.

Please help!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Maybe something like...

Unknown[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]Region[/TD]
[TD="bgcolor: #FAFAFA"]Var[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-38%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-46%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-12%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-25%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-74%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-36%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-45%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-47%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-81%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-74%[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-25%[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-36%[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-12%[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D15[/TH]
[TD="align: left"]{=MAX(IF($B$2:$B$11=C15,$C$2:$C$11))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Perhaps:

ABCD
Location

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Location[/TD]
[TD="bgcolor: #FAFAFA"]Region[/TD]
[TD="bgcolor: #FAFAFA"]Var[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-38%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-46%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-12%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-25%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-74%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-36%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-45%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-47%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-81%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-74%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Region[/TD]
[TD="bgcolor: #FAFAFA"]Max Var[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]484[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-25%[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]521[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-36%[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]803[/TD]
[TD="bgcolor: #FAFAFA, align: right"]-12%[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C15[/TH]
[TD="align: left"]{=LARGE(IF($B$2:$B$11=B15,$C$2:$C$11),1)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D15[/TH]
[TD="align: left"]{=INDEX($A$2:$A$11,MATCH(B15&"|"&C15,$B$2:$B$11&"|"&$C$2:$C$11,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
=INDEX($C$3:$C$12,MATCH(MAX(($B$3:$B$12=B15)*ABS($C$3:$C$12)),ABS($C$3:$C$12),0))

Confirm with CTRL-SHIFT-ENTER rather than just Enter. Copy down.


Excel 2010
ABC
1A B C
2LocationRegionVar
31484-38%
42521-46%
53803-12%
64484-25%
75484-74%
86521-36%
97803-45%
108803-47%
119521-81%
1210484-74%
13
14
15484-74%
16521-81%
17803-47%
Sheet1
Cell Formulas
RangeFormula
C15{=INDEX($C$3:$C$12,MATCH(MAX(($B$3:$B$12=B15)*ABS($C$3:$C$12)),ABS($C$3:$C$12),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thank You! the Large function worked perfectly. I also figure out that some of my cells were in text and not numeric value which is why my formula was also not working.
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Large Function with Multiple Criteria Question, Ranking
Replies
6
Views
2K
Scott T
Scott T
G
  • Solved
Sort/filter formula used for search formula combobox issue with shorter named values, Formula help for search combobox
Replies
6
Views
838
Akuini
Akuini
C
  • Question Question
LARGE formula that finds consecutive numbers
2
Replies
12
Views
1K
DanteAmor
DanteAmor
S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
993
AlanY
A
T
  • Question Question
SUMPRODUCT with multiple criteria in Rows and Columns (Still function as SUMPRODUCT instead of SUM or COUNT)
Replies
4
Views
1K
trbharat
T

Forum statistics

Threads
1,224,618
Messages
6,179,916
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top