Excel function

[Candentco Creative Collection]

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

 

[Bob Phillips]

=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
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Harlan Grove]

=IF(SUM(E2:E2)=A2,0,IF(AND(SUM(C2:E2)<A2,SUM(C2:E2)>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*$B2,$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.

 

[Candentco Creative Collection]

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

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

 

[Candentco Creative Collection]

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!

 

[Candentco Creative Collection]

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!