Excel Formula to Adjust certain rows to give a resulting reference cell

You can't have a value and a formula in the same cell. This formula, in D3, copied across, does what you wanted:

=IF(IF(SUM($D$2:D2)>=$B$2,D2-(SUM($D$2:D2)-1000),SUM($D$2:D2))<=0,"",IF(SUM($D$2:D2)>=$B$2,D2-(SUM($D$2:D2)-1000),SUM($D$2:D2)))

Yep. My mistake. Corrected (if I understand your requirement.
=IF(IF(SUM($D$2:D2)>=$B$2,D2-(SUM($D$2:D2)-1000),SUM($D$2:D2))<=0,"",IF(SUM($D$2:D2)>=$B$2,D2-(SUM($D$2:D2)-1000),D2))

Let me know if this is correct...

Thanks Glenn. This was my requirement. But is it possible to get the result in a single cell showing the adjusted amount only without dragging it horizontally.

Thank you so much. But could you please explain me the logic so that I can understand better.

I'll explain the logic when it's finished. No point yet. A picture is worth a 1000 words. If this is not what you want, please explain by posting a sheet showing what you REALLY want. I'm not prepared to keep guessing forever!!!

However, you do need a formula in every cell - as you cannot tell a priori which cell will need to be adjusted.

This monster will do it. However, how will you be able to tell WHICH value is the subject of the adjustment, in this case???

I don't think that you can have the very last part. However, I can get the adjusted amount in red font.

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

I've spent much of the day working a chainsaw and I'm tired. I'll have a reply for you in the UK morning.

In the cold light of morning... there's an easier way to do this. Newdoverman suggested one simplification, but this is simpler still.

The formula is split up into 4 similar units, one for each of the cells in D to F the result from each unit is added together to give a final result.. The first unit considers D2, the second considers the sum of D2+E2, the third considers the sum of D2+E2+F2, etc.

Just look at the first line in the formula:

IF(SUM($D$2:D2)>$B$2,IF(D2-(SUM($D$2:D2)-$B$2)<0,0,D2-(SUM($D$2:D2)-$B$2)),0)

IF(SUM($D$2:D2) IF the running total

>$B$2 is greater than the target value ...

IF(D2-(SUM($D$2:D2)-$B$2) IF the running total minus the target, subtracted from the value in the first cell

<0,0 is less than zero, return zero, otherwise...

D2-(SUM($D$2:D2)-$B$2)) return D2 minus the difference between the running total and the target. however, (back to the beginning of the formula again)

IF(SUM($D$2:D2)>$B$2,things that happen if this is TRUE,0) If the running total is NOT greater than the target value, return zero.

Same for each of the 4 units in the formula.

I have amended the CF with the simpler formula. i also added in a bit more CF to grey-shade those cells AFTER the adjusted cell.

Nine formulae, actually. One monster for the result cell and two CF formulae in EACH of the 4 cells to deliver the red font or grey shading.

I do not understand your comment. As far as I am concerned, it is giving the correct result. 1000 in J2. What were you hoping to see and where?

Home/Number/Accounting Number format and select dollars.

Another change of requirement!!

Do the new rows refer to the same cell (B2) or to B3, B4, etc, as you copy down?

Another change of requirement!!

Do the new rows refer to the same cell (B2) or to B3, B4, etc, as you copy down?

Can you populate one or two more rows with your expected answers?

MORE IMPORTANTLY: are there any further surprise requirements that you have hidden away somewhere?????

No reply as yet. So.. I'll have a LAST guess.

So, that is what my attachment at Post 26 does. hopefully the problem IS now solved....