Complex Excel Formula Question

[JeffK627]

Hello All,

I have a seriously complex set of conditions that need to be represented in one cell in each row of a worksheet. Here's what I need to happen:

If column D, Row x is		Column K, Row x needs to be
Category A		        Same as Column G, Row x
Category B		        Same as Column G, Row x
Category C		        Same as Column G, Row x
Category D		        Column F, Row x * AVG(Column I where Column D = Category C)
Category E		        ((AVG(Column I where Column D = Category C) * Column F, Row x) / (1-(AVG(Column I where Column D = Category C)))
Category F		        Same as Column G, Row x
Category G		        Column F, Row x * AVG(Column I where Column D = Category F)
Category H		        (Column F, Row x * AVG(Column I where Column D = Category F)) / (1-(AVG(Column I where Column D = Category F)))

Is there a way to do this without a ridiculous amount of ORs, IFs and COUNTIFs?

Thanks in advance!

 

[mikerickson]

Here is one approach

=($K1 = CHOOSE(MATCH($D1,{"A","B","C","D"},0), $G1, $G1, $G1, $F1 * SUMPRODUCT($I$1:$I$999, --($D$1:$D999=$C$1:$C999))/SUMPRODUCT(--($D$1:$D999=$C$1:$C999))) )

 

[JeffK627]

Thanks mikerickson - this is a bit advanced for me, but I'll try to figure it out!

 

[mikerickson]

MATCH($D1,{"A","B","C","D"},0) returns 1,2,3 or 4 depending on what is in D1

CHOOSE(MATCH(...), ) reuturns the value g1, g1, g1 ,F1*(your average) depending on what is in D1

K1 = CHOOSE(...) tests if K1 matches those values

Thus what is in D1 determines the MATCH value. CHOOSE of that determines what value is going to be compared to K1.

 

[JeffK627]

OK, I think I've got it - thanks!

 

[JeffK627]

OK, actually, I ain't got it...

The problem is, depending on which of the categories is in Column D, I need to put a different formula in column K. The formula you gave me does that, but some of the formulas are "normal" (e.g. "=B1/C1") and some are array formulas. I can't use "K:K.FormulaArray=" before the CHOOSE, it throws an error (Unable to set the FormulaArray property of the Range class).

So - is there a way to use the same CHOOSE function to enter either a simple formula or an array formula in a cell?

 

[mikerickson]

None of those functions are array functions. The terms of SUMPRODUCT are arrays, but it's not.