tricky formula

[Allainm]

I need a formula that will take a number from column A and divide that total starting number into equal values over a variable series of cells in the same row.

Example column A1 = 100, and the formula is copied and pasted across 10 corresponding columns on row 1, the returned value would be 10. If it is only copied to 5 columns the answer would be 20, 4 columns the answer would be 25 etc. The selected columns could start at column B, or column G, or column ZZ. Where it starts doesn't matter so long as the range of cells copied to is a continuous range. (We want to be able to drag the bottom right corner of a cell to draw the range).

The second criteria would allow a manual insertion of a typed in value which the formula would take into account and adjust the other values still containing the formula. In the example of A1 = 100 and the formula is copied over 10 cells, if D1 was to have 19 entered as a value, the other 9 cells would take that into account and execute a result of A1-19=81...81/9 remaining cells should return equal values of 9 in the 9 remaining cells.

 

[Marcelo Branco]

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][td="bgcolor: #DCE6F1"]

N​

[/td][td="bgcolor: #DCE6F1"]

O​

[/td][td="bgcolor: #DCE6F1"]

P​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

100​

[/td][td][/td][td][/td][td]

19​

[/td][td][/td][td][/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td]

9​

[/td][td][/td][/tr]
[/table]

Formula in G1 copied across
=($A1-$D1)/(COUNT(H1:$Z1)+COLUMNS($G1:G1))

Hope this helps

M.

 

[Allainm]

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

A​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

B​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

C​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

D​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

E​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

F​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

G​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

H​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

I​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

J​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

K​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

L​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

M​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

N​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

O​

[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

P​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]

1​

[/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

19​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

9​

[/TD]
[TD]

9
​

[/TD]
[TD]

9​

[/TD]
[TD]

9
​

[/TD]
[TD]

9​

[/TD]
[TD]

9​

[/TD]
[TD]

9​

[/TD]
[TD]

9​

[/TD]
[TD]

9
​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in G1 copied across
=($A1-$D1)/(COUNT(H1:$Z1)+COLUMNS($G1:G1))

Hope this helps

M.

Hi Marcelo, thank you for the reply. But it doesn't quite satisfy the first criteria. A1=100 is good, but D1=19 is outside of a continuous range of cells (required). For your example to be valid it would have to run F1:O1 or B1:K1. Then 19 entered into any cell in the range would yield 9 in all of the other cells resulting in a sum of the range of cells = to the number in A1 of 100. The sum of the range must always equal the number in A1. Each cell in the range will either be a manual entry or a result of the formula subtracting any/all manual entries within the range from the total in A1 and dividing the remained equally among the cells within the range that still contain the formula.

The range can be across any number of columns from a single column to dozen's of columns.
The ranges can be copied t any number of rows

2nd Criteria would be if the range were shortened to 5 columns, the formula would return a result of 20 in each field, and if any cell in the range were to have a manual entry of 40, the result in the other cells in the range would change to 15.

 

[Marcelo Branco]

The cell to be subtracted from A1 must be known (fixed) - otherwise how would Excel (the formula) know which cell to subtract? And such cell should be outside of the range where the formula will be copied (dragged to the right) - otherwise the cell would be overwritten when the formula is dragged..

What you want, as far as i know, is not possible with formulas. Maybe with a macro using the Worksheet_Change event.

M.