1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel formula

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

Thread Status:
Not open for further replies.
Advertisement
  1. lemoof

    lemoof Thread Starter

    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

    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.)
     
  3. lemoof

    lemoof Thread Starter

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

    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
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/947104

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice