Sorry to waste everyone's valuable perusing time, but I solved my latest
mystery with the following:

=ROUND(IF(SUM($I17:I17)=$F17,0,IF($F17-(SUM($I17:I17))3*$H17,3*$H17,$F17-SUM($I17:I17))),0)

Have a good one!



--
MVD
San Francisco, California


"Candentco Creative Collection" wrote:

Again I come to the tank for a solution...

When I copy the following formula:

=ROUND(IF(SUM($I17:I$17)=$F17,0,IF($F17-(SUM($I17:I$17))3*$H17,3*$H17,$F17-SUM($I17:I$17))),0)

down one cell, the result is:

=ROUND(IF(SUM($I$17:I18)=$F18,0,IF($F18-(SUM($I$17:I18))3*$H18,3*$H18,$F18-SUM($I$17:I18))),0)


when my goal is:

=ROUND(IF(SUM($I18:I$18)=$F18,0,IF($F18-(SUM($I18:I$18))3*$H18,3*$H18,$F18-SUM($I18:I$18))),0)


The area of issue is the ($I18:I$18) which repeats another two times in the
formula. Why does it change where the $ signs are? ...and is there a way to
accomplish by objective here?

Molti grazie!




"Harlan Grove" wrote:

"Bob Phillips" wrote...
=IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E 2)B2*3),
B2*3,SUM(C2:E2)))
....

I suspect your first SUM term is a typo and you meant

=IF(SUM(C2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2 )B2*3),
B2*3,SUM(C2:E2)))

However, I don't think that's what the OP requested. My take on it is

=IF(SUM($C2:E$2)=$A2,0,IF(SUM($C2:$E3)3*$B2,3*$B 2,$A2-SUM($C2:$E2)))

with the column-absolute references due to the OP's 'sum of column c
through one cell before Col f', which leads me to suspect the formula
would go into F2 then be filled right. I've seen residual amounts
spread in similar manner before.