Help with Excel Data Tables

Newton

I'm trying to create a table in Excel that has two inputs and one output. Basically, I have a column of data that is decaying that is the input to cell A1, with A2 another variable and A3 is the product of A1 and A2. I can increase A2 up to a point to compensate for the decay in A1 to try and maintain a constant value in A3. I would like to automate this somehow, as it currently is rather time consuming to fill in a table manually making the changes and it isn't very easy to test different decay scenarios. Any ideas on how to do this? Are there built in functions that I can use, or will I have to write a macro to do it? Thanks for the help.

rfalias

Are you just trying to do A1 + A2 = A3? (or some other operand)

Or do you want it to just put both fields together in A3, like for example: A1 and A2 placed together in field A3

A1 A2 A3
99 05 99 05

Kind of confusing, but trying to fully understand the problem. Any specific examples you can provide would be great.

Newton

This is kind of what I'm trying to do. The first column has data that is decaying and the second column has a variable that can be increased to compensate for the decay, but it has a maximum value. The third column is the output, which is basically a product of the first two columns. I'm trying to adjust the value in the second column to maintain the value in the third column until C2 hits the limit.

C1   C2      C3
10    2     20
9    2.22   20
8    2.5    20
7    2.86   20
6    3.33   20
5    4      20
4    4      16
3    4      12
2    4      8
1    4      4

rfalias

Ok, if I understand this correctly then:

C1 and C3 are constant, you want C2 to be automatic so that it finds the necessary value to multiply by C1 to get what is in C3. If that is the case then just enter this into C2:

=C3/C1

Then just drag the little square at the bottom right and fill out the rest of the cells for the sheet.. (Autofill? I forget the name of it)

localh77

The formula you want in the middle column is "MIN(<desired result>/A1,<max value>)", and then the right column will have "A1*B1". In your example:

10     =MIN(20/A1,4)     =A1*B1
 9     =MIN(20/A2,4)     =A2*B2

etc.

Note: You'll have to know ahead of time which product you want in the third column (hence the hard-coded 20), or else you'll have a circular reference.