Excel function
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.
|