Excel formula question

[toythatkills]

I've not used Excel in years and I've no idea if this is actually possible, so hopefully someone out there knows whether it is/how to do it if it is!

Can it be set up to autofill a column based on numbers/results of sums in other rows/columns, so that the totals in another column all make positives? The main problem being that the results in the (potentially) autofilled column would affect the sums in other columns. As I'm typing this it sounds mental.

If it sounds remotely possible I can give more specific details. I'd really appreciate some help setting it up if anyone has time/can be bothered!

[kryss]

...Could you explain it a little more? Maybe being a little more specific about the kind of data you'd be using.

[toythatkills]

Column A would be numbers that I input. One per row, obviously. Usually between 5 and 10 of them.

Column B would be a multiplier, and the multiplier for the largest number of column A would be set at 2.

Each row in column C would be the results of A*B and minus the total of all the numbers in column B. Each row and must equal a positive number.

What I want it to do is have it autofill column B so that every row in column C produces positive numbers. What makes it more difficult though is that every number you add to column B effects all the results in column C.

I can't work out if that makes sense...

[tonyk100]

If column A is in groups of 5 or 10 rows, and Col B multiplies the 5 - 10 rows, with numbers below 2, with 2 being the largest in the set in A, with the result of the multiplication in column C less the factors, what do you mean by add a number to column B.

Do you mean that if you add a New figure in column A that becomes the highest the multiplciation factor in B should be resorted to keep the '2' factor on the highest row. if so then I think that you could could use a nested if statement with a MAX(A1:A10) to keep the focus on the highest A.

Sometimes its easy to give some details on what your trying to do, and a macro might be best to refresh and rerank the multitpictions in B.

T

[babs]

I can probably do it but I really can't make sense of what you're asking.

[toythatkills]

I'm really not sure how to best explain it! Column A I would always complete initially myself, so for example

A
5
6
7
8

In that example, with 8 being the largest number, it would be given a factor of 2 in column B, making the result in column C for that row 14 (8*2-2).

I want to be able to work out what numbers to use in column B for the remainder of the rows to generate positive numbers in each row for column C. If in the row for 7 I also used a factor of two, that would still give me a positive (7*2-4=10) the number subtracted being 4 this time because the total from column B is what needs to be subtracted. Row '8' also will be amended down by 2 to 12 but still remains positive. And so on.

What I need is for the remainder of column B to be filled aside from the 2 to generate positive numbers on all rows in column C. So the red numbers here. Using 4 for the other two numbers works for this example and the results in column C all come up positive.

A....B.........C
5 * 4 -12 = 8
6 * 4 -12 = 12
7 * 2 -12 = 2
8 * 2 -12 = 4

12 being subtracted as the total of the figures in column B

I'm not sure if that's any clearer or not, tbh. It's really hard to explain!

[Psyduck]

I get what you're asking (though not why!), I don't think you'll be able to do it without macros, formulae will end up with a circular reference and be unhappy.

[tonyk100]

So the highest in the sequence isn't automatically assigned a 2 then?


A....B.........C
5 * 4 -12 = 8
6 * 4 -12 = 12
7 * 2 -12 = 2
8 * 2 -12 = 4

because otherwise you could use 1.001, 1.002 as the multiplier and be OK.

As I said before could you not explain a bit more what these figures refer to, hours worked, sales per area etc, it would make discussing it easier. You don't often work negative hours, but if its finaces its more likely. Knowing more even if you just use a simple metaphor, might help.

As psyduck says you will quickly stumble into the circular reference trap. Is it possible to add another column i.e. D which could be hidden later.

e.g.

in column C

=RANK(A2,$A$2:$A$50,1)

formula in B

=IF(A2=MAX($A$2:$A$50),A2*2,A2*(1+(C2/100)))

would allow you to add numbers to col A the largest would be assigned 2 as per your second post the rest a percentage of the rank.