check if sum of column is below a value, if not increment adjacent cell value by previous cell +1 1

[thomasks]

I have a column of values in I3 that I need to check the sum until I reach a threshold of 24.5 (full load amps). If the value exceeds 24.5 the cell J3 (branch number) needs to increment by the value of the previous cell in the row above plus 1. The first row will always start at 1.
So if rows I3-5 sum reaches 25, then cells J3,4 would equal 1
Then it would evaluate the cells in I5,6,7 etc. until it reached 24.5 then increment cells j5,6,7 to 2. And so on.

Thanks,
Kirk

 

[SkipVought]

hi,

I have [highlight #FCE94F]a column of values[/highlight] in I3

How can you have "[highlight #FCE94F]a column of values[/highlight]" in one cell?

check the sum

Do you mean that the SUM for the values in some range is in I3?

J3 (branch number) needs to increment by the value of the previous cell in the row above plus 1.

Do you mean:
[tt]
J3: =J2+1
[/tt]
...and copy that formula down?

This is not explained very clearly.

Please post a concrete example with columnar data, along with an explanation of what you expect.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

Perhaps you should try to describe what you need to achieve. Not how you think it should be done.

Goal Seek or Solver would seem a possible solution.

 

[SkipVought]

The WHAT is always a better start than the HOW.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[thomasks]

Sorry I was not clear in my question.
I have values in the column for full load amps (FLA). I need to check the sum of the values cells in the column until I reach a threshold of 24.5. Once the threshold is reached, then I have to start summing the values again from where it left off, and increment the value in the (Branch) column by 1

A B C D E F
1 Unit HP VFD Brake FLA Branch
2 4100 30 Yes No N/A 0
3 4104 5 No Yes 7.6 1
4 4106 2 No No 3.4 1
5 4108 2 No No 3.4 1
6 4109 5 Yes No 7.6 1
7 4110 5 Yes No 7.6 2

 

[mintjulep]

Perhaps you should try to describe what you need to achieve. Not how you think it should be done.

 

[SkipVought]

where does the N/A come from? What does that mean? Why branch 0?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[thomasks]

The first one is controlled externally and is not calculated for the cabinet. I should have left that one out.
These are circuits that must be limited to 24.5 amps per branch. I just want to go down the column adding up the full load amps till I reach 24.5 then assign it to a branch and continue with the next branch after that.

 

[SkipVought]

[b]
Unit	HP	VFD	Brake	FLA	1[/b]
4104	5	No	Yes	7.6	1
4106	2	No	No	3.4	1
4108	2	No	No	3.4	1
4109	5	Yes	No	7.6	1
4110	5	Yes	No	7.6	2

where the formula in F2:

[tt]
F2: =IF(SUM($E$2:E2)>AmpLimit,F1+1,F1)
[/tt]

...and where AmpLimit is the range name for the cell containing 24.5

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

Sorry Skip, but you need to reset the SUM starting row each time you get to the AmpLimit.

Much easier if you add a helper column.

=IF((B1+A2)>24.5,A2,B1+A2) =IF(AND((B1+A2)<=25.4,B2>=B1),C1,C1+1)

.

 

[SkipVought]

sorry, I've got other stuff going on...

[tt]
F2: =INT(SUM($E$2:E2)/AmpLimit)+1
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[madonnac]

As I assume you're trying not to overload a circuit, we will have to restart the numbering when the next value would overload the previous branch.

This requires a helper column.

[pre]Unit HP VFD Brake FLA Branch load Branch #
4104 5 No Yes 7.6 7.6 1
4106 2 No No 3.4 11 1
4108 2 No No 3.4 14.4 1
4109 5 Yes No 7.6 22 1
4110 5 Yes No 7.6 7.6 2
4111 5 No Yes 7.6 15.2 2
4112 2 No No 3.4 18.6 2
4113 2 No No 3.4 22 2
4114 5 Yes No 7.6 7.6 3
4115 5 Yes No 7.6 15.2 3
[/pre]

The formula for the Branch Load would be =E2 in F2, and =IF(F2+E3>24.5,E3,F2+E3) in F3 onward
The formula for the branch number is 1 in G2, and =IF(F3=E3,G2+1,G2) in G3 onward

 

[thomasks]

Yes that is exactly right. Thanks for the help!