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 help - "Plant a forumla"

Discussion in 'Business Applications' started by beast78, May 16, 2005.

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

    beast78 Thread Starter

    Joined:
    Jun 29, 2003
    Messages:
    20
    Hi guys...I am pretty much a novice at Excel, and I need some help.

    Is there anyway that I can "pre-plant" a forumla? That means, for instance, I want all the cells of the whole of 1 row/column to have a mathematical function, so that when I key in the value, the cell will automatically display the calculated value. E.g. I want the whole of column A contains (value *1.05), so when I key in 100 in Cell A1, I get 105, and 300 in cell A2, I get 315...

    I know it is way much easier to use 1 column to key in the data, and use another column to use the formula of "=A1*1.05" and just drag the formula downwards...Just wondering if it is possible to do it without using 2 columns, and if possible, without using Macro as well.

    Thanks for any help.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    There is no way to do this without using a macro or creating a custom funtion using VBA.


    Rollin
     
  3. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> I know it is way much easier to use 1 column to key in the data,
    >> and use another column ...

    An alternative ; key in the data, enter 1.05 in B1, copy B1, select the data in A, Edit -- Paste Special -- Multiply -- OK.

    Rgds,
    Andy
     
  4. beast78

    beast78 Thread Starter

    Joined:
    Jun 29, 2003
    Messages:
    20
    Thanks for the reply Rollin. I have not used macro or VBA before so I don't have a clue on how to go creating a function for it. Could you please further elaborate on how to use macro or VBA to go about doing it?

    P.S. Thanks for the reply too Andy.
     
  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> please further elaborate on how to use macro or VBA

    Excel Help is pretty thorough on macros for beginners ; see "Record a macro".

    It would take very little code to "up" a selected range of values by 5%, eg:

    For Each Cell In Selection
    Cell.Value = Cell.Value * 1.05
    Next Cell


    Rgds,
    Andy

    EDIT: actions performed by macros cannot normally be undone by the usual methods. Take precautions (save your work) before doing anything if not 100% sure.
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    First, make sure macros are enabled in the workbook by clicking TOOLS ---> MACROS ---> SECURITY and setting the security level to either medium or low. Next, press ALT + F11 to bring up the Visual Basic editor. On the the Visual Basic Editor choose INSERT ---> MODULE and paste the following code into the blank module and re-save the workbook.

    Code:
    Public Function Calc(vNum As Single)
    
    Calc = vNum * 1.05
    
    End Function
    
    Now you have a "User Defined" function that you can call from the workbook. Just type =Calc() and place the number you want recalculated inside the paranthesis.

    You can also do as Andy implied earlier and paste his code into the blank module instead and then make your selection of cells on the workbook and run his code from the macro menu (TOOLS ---> MACRO ---> MACROS and then select the macro from the list)

    If it was me, I would probably follow the earlier suggestion just to put the number 1.05 if an empty cell and then copy it and then select Edit ---> Paste Special ---> Multiply


    Rollin
     
  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/362777

  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