Excel formula

Discussion in 'Business Applications' started by lemoof, Sep 1, 2010.

Not open for further replies.

Joined:
Aug 31, 2010
Messages:
3
Hi,

Can someone please tell me how this formula works:

{=SUM((\$F\$74:\$F\$123)*(\$M\$74:\$M\$123>=G135)*(\$W\$74:\$W\$123>=G135))/4*(1+Data!\$AI\$23)^(G135/4)}

All the data is numerical and the result that I get from the formula is simply (\$F\$74:\$F\$123)/4.

Thanks.

2. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
First, you should be getting a numerical result, so click in the cell, go to Format, Cell and select General. Other formats may work, but apply them later.
Next, the {} means this is an array formula (some people call them CSE formulas - you will see why soon). You don't type the {} in yourself - you type in the formula and hold down Control, Shift and hit Enter (hence CSE) and Excel adds the brackets automatically.
Next, you have several ranges, such as \$F\$74:\$F\$123. Just for clarity, I will point out that the range could be written as F74:F123 but that would be a relative range - if you copied the formula and pasted it elsewhere, Excel would alter the range to match. Having the \$ in the formula makes these absolute values, so no matter where the formula is pasted, the range looked at is always this one (or, rather, these ranges.)
Your formula does several things, so let me break them up.
{=SUM((\$F\$74:\$F\$123)*(\$M\$74:\$M\$123>=G135)*(\$W\$74:\$W\$123>=G135))/4*(1+Data!\$AI\$23)^(G135/4)}
Any portion like
=SUM(\$F\$74:\$F\$75)>=G135 (also an array formula)
looks at the sum in the range to see if it is greater or equal to the value in G135. (If it isn't (in any of the comparisons) you get a value of 0.)
The next batch is more of the same - just algebra.
Finally, (1+Data!\$AI\$23)^(G135/4) takes the value on the sheet called Data in cell A23, adds 1 to it and raises the whole thing to the value of whatever is in cell after dividing that value by 4 (e.g., if the value in Data!\$AI\$23 was 9 and that in G135 was 8, it would add 1 to the 9 to get 10, then take it to the power of 8/4 or 2, making the value of 100.)

Joined:
Aug 31, 2010
Messages:
3
Thanks a lot for the feedback. Very useful.

4. slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Were you able to alter your format to General so that you get a numerical result? I tried out your formula and it works for me.
If you did get this to work and have no further questions, please use the button at the top of the page to mark this thread as Solved.

See you around!

As Seen On