Excel function
 

I am try to do the following: (i.e....)

Col A Col.B Col C. Col. D Col E col. F
$50,000 $2,000 $2500 $1,000 $5,000 ???


if the sum of columns c through e is less than col A and greater than Col. B
times 3, then return col. b times 3. if not, return col a minus sum of
column c through one cell before Col f Also, if sum of c thru e is greater
than or equal to col a, then reutrn 0

Excel function
 

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

I think

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Candentco Creative Collection" <Candentco Creative
wrote in message
...
I am try to do the following: (i.e....)

Col A Col.B Col C. Col. D Col E col. F
$50,000 $2,000 $2500 $1,000 $5,000 ???


if the sum of columns c through e is less than col A and greater than Col.
B
times 3, then return col. b times 3. if not, return col a minus sum of
column c through one cell before Col f Also, if sum of c thru e is
greater
than or equal to col a, then reutrn 0

Excel function
 

"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.

Excel function
 

Bob, thanks for the effort and turning on the light.

Harlan, thank you, sir, for the eclipse! You were right on!!!




"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.

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.

Excel function
 

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.