Brackets

[Khalil Handal]

Hi,
My formula BH28 is:
=IF((AND(BD3=1,BE3=1)),$BK$20,(IF((AND(BD3=2,BE3=1)),$BK$21,IF((AND(BD3=3,BE3=1)),$BK$22,$BJ$22),$BJ$21)),$BJ$20)

I think the problem is with the brackets!
Please help

 

[Don Guillett]

What are you trying to do?

 

[Khalil Handal]

This is what I am doing:

Cell BD3 has either 1, 2, or 3
Cell BE3 has either 1 or 2

I need a formula in cell BG3 with the following:

if BD3=1 and BE3 =1 value in cell BG3 will equal to $BK$20
if BD3=2 and BE3 =1 value in cell BG3 will equal to $BK$21
if BD3=3 and BE3 =1 value in cell BG3 will equal to $BK$22
if BD3=1 and BE3 =2 value in cell BG3 will equal to $BJ$20
if BD3=2 and BE3 =2 value in cell BG3 will equal to $BJ$21
if BD3=3 and BE3 =2 value in cell BG3 will equal to $BJ$22

I hope this is clear !
I need to copy the formula until rwo 20.

 

[Niek Otten]

One way:

=CHOOSE(BD3,IF(BE3=1,$BK$20,$BJ$20),IF(BE3=1,$BK$21,$BJ$21),IF(BE3=1,$BK$22,$BJ$22))

Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Don Guillett]

Another way using INDIRECT
=INDIRECT(IF(bE3=1,"bk2","bJ2")&CHOOSE(bD3,0,1,2))

 

[Dana DeLouis]

Hi. This doesn't check your input, but maybe ...

=OFFSET(BJ20,BD3-1,2-BE3,1,1)

= = = = = = = = = =
HTH
Dana DeLouis

 

[Don Guillett]

Nice

 

[Rick Rothstein]

You can save a mathematical operation (subtracting the 1) by moving your start cell up one row...

=OFFSET(BJ19,BD3,2-BE3,1,1)

 

[Dana DeLouis]

Hi. I thought I would be called on the ending ...1,1 as being
unnecessary. I keep forgetting that. :>)

=OFFSET(BJ20,BD3-1,2-BE3)

= = = =
Dana DeLouis

 

[Rick Rothstein]

I didn't call you on that, at least not directly<g>, as there are a lot of people who like specifying all parameters (whether optional or not) for what they consider completeness sake. Of course, as you can see from the modified formula I posted (which you didn't use, by the way), I'm not one of these people.

 

[Khalil Handal]

Niek and Don's formuls gives #value! error if BD="" while Dana's formuls
gives 0 if BD="" which works better for me since I will use the 0 with
other formulas.

Thanks to all of you, all works well.

 

[Harlan Grove]

Cell BD3 has either 1, 2, or 3
Cell BE3 has either 1 or 2

I need a formula in cell BG3 with the following:

if BD3=1 and BE3 =1 value in cell BG3 will equal to $BK$20
if BD3=2 and BE3 =1 value in cell BG3 will equal to $BK$21
if BD3=3 and BE3 =1 value in cell BG3 will equal to $BK$22
if BD3=1 and BE3 =2 value in cell BG3 will equal to $BJ$20
if BD3=2 and BE3 =2 value in cell BG3 will equal to $BJ$21
if BD3=3 and BE3 =2 value in cell BG3 will equal to $BJ$22

....

All those volatile OFFSET calls from the other respondents. Try a
nonvolatile function call.

=INDEX($BJ$20:$BK$22,BD3,3-BE3)

or if you really, really want to avoid any & all arithmetic, try

=INDEX(CHOOSE(BE3,$BK$20:$BK$22,$BJ$20:$BJ$22),BD3)

 

[Harlan Grove]

Niek and Don's formuls gives #value! error if BD="" while Dana's formuls
gives 0 if BD="" which works better for me since I will use the 0  with
other formulas.

....

You didn't say before that BD3 could be blank. Dana's formula would
return 0 in that situation when your cells BJ23:BL23 are blank, but
that's not particularly robust. You'd be better off using something
like

=IF(COUNTA(BD3:BE3)=0,0,base_formula_here)

which produces a definite result if BD3 and BE3 are both blank (in
which case the COUNTA call returns 0).

 

[Khalil Handal]

Thanks,

...

All those volatile OFFSET calls from the other respondents. Try a
nonvolatile function call.

=INDEX($BJ$20:$BK$22,BD3,3-BE3)

or if you really, really want to avoid any & all arithmetic, try

=INDEX(CHOOSE(BE3,$BK$20:$BK$22,$BJ$20:$BJ$22),BD3)

 

[Dana DeLouis]

=OFFSET(BJ19,BD3,2-BE3,1,1)

Ok. :>) How about shifting the corner of the table to the upper right?
Hence, getting rid of the 2.

=OFFSET(BL19,BD3,-BE3)

= = = = = = = =
:>)
Dana DeLouis

 

[Rick Rothstein]

Good thought... I like it. Of course, Harlan has made a good comment about
using INDEX instead of OFFSET though.