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 2000 Newbie Question-Formulas

Discussion in 'Business Applications' started by cruachaun, Sep 12, 2003.

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

    cruachaun Thread Starter

    Joined:
    Sep 8, 2003
    Messages:
    9
    I have a spreadsheet where a number is entered each month ( central
    heating gas consumption ) and I then need to employ the following
    operations to that number each month :-

    1.the number in the cell ( A1 say ) to be multiplied by 11.4612

    2.the answer to this to be further multplied by 1.9757

    3.the answer arrived at in step 2 to be multiplied by 12.5%

    4.the answer to step 3 to be added to the answer arrived in step 2

    All of these operations to be applied each month as a new number is
    added in cells B1, C1 ...etc each month of the year. ( These numbers
    represent monthly gas consumption in cu.meters)

    I would also like to keep a running total of the figure arrived in
    step 4 after each nonths entry.

    Can all these calculations be done by use of a formula ?

    If so , how do I construct a suitable formula , please ?
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    You don't say if this is a megaformula, or if you need to keep the steps. Assuming the latter ...

    =A1*11.4612 entered in A2.
    =A2*1.9757 entered in A3.
    =A3*12.5 entered in A4.
    =A3+A4 entered in A5.
    =SUM(5:5) entered in A7 (running total).

    You can select A2:A5 and drag across using the fill handle (small black square at the bottom right of the selection).

    HTH,
    Andy
     
  3. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    XLG's question is a good one, Cru - Do you need to see the intermediate numbers? If so, then follow the steps he listed. If not, then perhaps consider simplifying it into one expression.

    In A2 you would have everything:

    =A1*11.4612 *1.9757 *1.125
    I made an assumption that you wanted to find 12.5 percent of the number in A3. Then since you added it back to A3 I made the assumption that you ultimately wanted to see 1.125% of that number - hence I merely multiplied it by 1.125. That's a lot of assumptions, I know, and so if I missed the mark you can adjust it all accordingly

    You wouldn't see any of the interum numbers - just the result.
    Do as XLG says and drag the formulas out to the right. Every column would have the monthly number in ROW 2

    somewhere in column A insert his =sum(2:2) and it would give the running total

    if the factors 11.4612 & 1.9757 are subject to change in the future you may consider placing them in their own cells and having the formula reference them.

    If though you need to see the intermediate numbers as they are calculated then you may employ XLG's work. He will be sure to remind me of that fact in the morning! :D

    - Castleheart
     
  4. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> He will be sure to remind me of that fact in the morning!

    Not at all -- in fact, I'd not long logged off before I realised I'd completely forgotten to mention that it's generally considered best practice to keep factors separate from the actual formulas, to make upgrading them in future easier, if necessary. Nice 1, CH.

    Rgds,
    Andy
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    It's also easier to give those "factors" a named range. So type those values in cells. Select the first value and Insert-Name-Define and call it (for instance) Factor1 or StateTax or something descriptive like that.

    Then you can change CH's formula to something like this:

    =A1*StateTax *UtilityTax * UseTax

    What they're saying is you can then change the value you named StateTax from 11.4612 to 11.4709 by changing just one cell instead of all the formulas.
     
  6. cruachaun

    cruachaun Thread Starter

    Joined:
    Sep 8, 2003
    Messages:
    9
    Thanks to all who've replied . Now that I've got the general idea I'll be able to experiment and get the results the way I want.

    Much obliged.
     
  7. Sponsor

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/164321

  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