+ Reply to Thread
Results 1 to 3 of 3

Applying a formula to a list of values

  1. #1
    Registered User
    Join Date
    09-18-2006
    Posts
    2

    Applying a formula to a list of values

    I have a spreadsheet that computes a formula. It takes an input into a cell, which gets operated on a few times (the output at the end of each step needs to be viewed) and it eventually results in a final output. What I want to do is, lets say I have a column of 6 values. I want to automatically pass each value to this formula, and have the final output be displayed next to each initial value.

    Input 1
    Add 8 9
    Multiply X 5 45
    Subtract 2 43
    Divide by 3 14.33333333
    Output 14.33333333

    This isn't really my formula, mines a lot more complex, this is just an example. So let's say I have a list of inputs
    1
    2
    3
    4
    5

    Rather than input each myself, and type the output of each next to each value, I'd like Excel to traverse the list, apply the formula to each value, and put the output next to the list of inputs to end up with this

    1 14.33333
    2 16
    3 17.66667
    4 19.33333
    5 21

    I want to keep the formula as part of an Excel worksheet, so that if I have to change the formula, I only need to do it in one place, and if I want to see any of the intermediate calculations on any of teh values, I just go to the formula, enter a value, and capture all the intermediate steps. I just don't want to manually enter a list of values if I'm only concerned with the final value of each. Thanks.

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,071
    Hi,

    My recommendation would be to create your own UDF ...UserDefinedFunction
    The easiest is, once for all, to use macro recorder for a macro to record all your steps ... and then transform the macro into your UDF

    HTH
    Cheers
    Carim

  3. #3
    Registered User
    Join Date
    09-18-2006
    Posts
    2
    Quote Originally Posted by Carim
    Hi,

    My recommendation would be to create your own UDF ...UserDefinedFunction
    The easiest is, once for all, to use macro recorder for a macro to record all your steps ... and then transform the macro into your UDF

    HTH
    Cheers
    Carim
    I was thinking about that, but theres about 100 steps in the formula, and the steps could potentially change, and I only want to change them in one place. Is there an easier way?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1