Array Formula does not return expected value when evaluated in one cell, but over two cell

Hi,

I am trying to use Array Formulas. All the components seem to work fine, but when I then try to evaluate it in just one cell I get unexpected results.

Please have a look at the attached Spreadsheet. Cell B15 contains the starting point of my array formula - all the values in range B15:BD15 are exactly what I expect them to be.....so all fine, so far.

Cell A15 now performs a sum over the range B15:BD15. The shown figure 4.05% is exactly what I would like to get as my end result. So I thought I could wrap a SUM() around the formula Array in B15 and evaluate it in Cell B16. Here is where my problem starts. No matter what I do, Excel refuses to return the value that I need: 4.05%. As you can see it returns 0%, which is obviously wrong - at least to me....

What I find interesting though is that all seems to be working fine if I expand the area of the array to Range B17:C17. Now the expected result is being shown in both cells. But unfortunately this is useless to me as I definitely need it to work on just one cell.

I am really confused. Maybe I am overlooking or not aware of some features of Array functions that I need to keep in mind.

Thank you for any hint what is needed in order to get this to work.

Best

Nevs

I think you are WAY overthinking your task.
You don't need array formula in B15:BD15.

In B15 and filled right, put
=IF(B3=$E$1,B7*B10,0)
Although, that's not really necessary if the ultimate goal is the result in A15.

In A15 put
=SUMPRODUCT((B3:BD3=$E$1)*(B7:BD7)*(B10:BD10))

Hope that helps.

Jonmo1,
thank you very much, that does it for me. I agree, I must have overthought the problem by a mile. I guess I need to better understand when to use the approach you described and when to use: {}


Arlu1201,
thank you for pointint this out to me. I was not aware of it. Jonmo1 answered my question. I will make a crosspost later today.

Jonmo1,
I was to quick to reply, and I guess I wasnt explicit enough in my initial question.

Row10 only displays an intermediate step, which ultimately needs to converge into the solution. What I ultimately need is to switch between the behavior in Cell F10 and the new formula that I need to get to work in an IF statement. Let me explain:

Cell F10 just references values in teh Ret! Sheet whenever Row2 has the value N. If Row2 has the value Y, then I need the weighted average of the Returns (so the values that I am displaying in Row 10 now, but that ultimately live in the Ret! Sheet). The weights that I need to use in order to come up with the weighted average are in Row 7. And I only need to multiply those returns and weights for those items where the value in Row3 (Group above row) is the same as the current group evaluated (Row1) - this is where the B3:BD3=$E$1 comes from.
This is a perfect example of a circular reference as I cant be displaying information in Row 10 that uses other cells in Row10.

So, long story short, the first answer to my original question gets it to work for the case I provided, but I guess I didnt provide the full complexity of the problem I am facing. I now that the components of the Formula that is in Cell E17, Excel just refuses to show the correct value unless I extend the area to at least two cells (E18:F18).

Thank you very much for re-considering my problem.

Nevs

You'll need to include the code for your reverse function. I'm guessing that's in an addin?
Or describe the purpose of the formula in Row 3.
Is that getting the text after the last _ ?

Also, that last book you posted, is the formula in A15 getting the result you want, but you want to get that same result without the intermediate cells in B15:BD15 ?

thank you all for your help in patience with me ultimately complying to all forum rules.... :-)