Find factor by target and given range

[mrwad]

I have a range on values, starting from `A1`, let's say:

Then I have a target, let's say: `43543`

Currently I am inputting values into Excel sheet and writing to cell `B1` formula `=CEILING(A1*$C$14, 1)` then dragging it down. In cell `C14` I have a factor, let's say `1.54`, then I am starting to manually adjust this factor in order to achieve `43543` as a result. In my example case I tried about 30 times until I have found that it should be `6.0588`

My question is: is there some formula that can automatically calculate this factor?

I have tried several solutions I have found, but can't get this working.

 

[awoohaw]

This is the best I can come up with but, it doesn't end up using integers as the values to sum.
I'm not sure how to get around the ceiling issue.

mr excel questions 22.xlsm
	A	B	C	D
1	234	1418
2	12	73
3	4356	26393
4	344	2085
5	234	1418
6	1214	7356
7	324	1964
8	234	1418
9	234	1418
10
11		Current Sum	43543
12		Target	43543	=C11/(SUM(A1:A9))
13		Factor	6.0588	6.059421097
Mr Wad

Cell Formulas
Range	Formula
B1:B9	B1	=CEILING(A1*$C$13,1)
C11	C11	=SUM(B1:B9)
D12	D12	=FORMULATEXT(D13)
D13	D13	=C11/(SUM(A1:A9))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1	234	1418
2	12	73
3	4356	26395
4	344	2084
5	234	1418
6	1214	7356
7	324	1963
8	234	1418
9	234	1418
10
11		Current Sum	43543
12		Target	43543
13		Factor	6.0594211
Lists

Cell Formulas
Range	Formula
B1:B9	B1	=ROUND(A1*$C$13,0)
C11	C11	=SUM(B1:B9)
C13	C13	=C12/SUM(A1:A9)

 

[mrwad]

Thank you for your answers. However these does not work. By changing target value to some other value, factor is changed but Current sum and Target does not match. I think the only option is to iterate through values using VBA. I have also noticed that in some cases it is not even possible to get factor even with precision 0.0000000001 and values has to be adjusted manually to get that last 1.

For example:

234​	2865​
12​	147​
4356​	53330​
344​	4212​
234​	2865​
1214​	14863​
324​	3967​
234​	2865​
234​	2865​
	Current Sum	87979​
	Target	87978​
	Factor	12.24297​

 

[awoohaw]

Well, they will work if you dont use the ceiling function!
But, aside from that. What is the objective of this exercise? Maybe there is another way to acheive your end result.