IF and conditional sum, one result

[pboost1]

Hello everybody. I am trying to do something with a spreadsheet and not sure how to work the formula. I have seven columns and seven rows. The column starts at F and the rows start and 1. The formula I have is this:

=IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))

This is in cell K6. I would like this formula to also be able to look at cells L6 and L7, and if L7 is larger than L6, subtract 1 from result of the formula in K6. Can something like this be done? Thank you in advance for any help that can be given.

[Excel_Geek]

Is this it?

=IF(L7>L6,(IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5))))))-1,IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5))))))

[Biff]

Hi!

If I understand what you want try this:

=IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))-(L7>L6)

Biff

"pboost1" <[email protected]> wrote in
message news:[email protected]...
>
> Hello everybody. I am trying to do something with a spreadsheet and not
> sure how to work the formula. I have seven columns and seven rows. The
> column starts at F and the rows start and 1. The formula I have is
> this:
>
> =IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))
>
>
> This is in cell K6. I would like this formula to also be able to look
> at cells L6 and L7, and if L7 is larger than L6, subtract 1 from result
> of the formula in K6. Can something like this be done? Thank you in
> advance for any help that can be given.
>
>
> --
> pboost1
> ------------------------------------------------------------------------
> pboost1's Profile:
> http://www.excelforum.com/member.php...o&userid=11895
> View this thread: http://www.excelforum.com/showthread...hreadid=398300
>

[rahrah3a]

try this. I nested your formula within an if statement.

if (L7>L6,your formula -1, your formula)

=IF(L7>L6,IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))-1,IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5))))))

"pboost1" wrote:

>
> Hello everybody. I am trying to do something with a spreadsheet and not
> sure how to work the formula. I have seven columns and seven rows. The
> column starts at F and the rows start and 1. The formula I have is
> this:
>
> =IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))
>
>
> This is in cell K6. I would like this formula to also be able to look
> at cells L6 and L7, and if L7 is larger than L6, subtract 1 from result
> of the formula in K6. Can something like this be done? Thank you in
> advance for any help that can be given.
>
>
> --
> pboost1
> ------------------------------------------------------------------------
> pboost1's Profile: http://www.excelforum.com/member.php...o&userid=11895
> View this thread: http://www.excelforum.com/showthread...hreadid=398300
>
>

[Biff]

Hi!

If I'm following you correctly then you can shorten your formula to:

=IF(SUM(K1:K5)<10,SUM(K1:K5),SUM(K1:K5)-LOOKUP(SUM(K1:K5),{10,10;20,20;30,30}))-(L7>L6)

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> If I understand what you want try this:
>
> =IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))-(L7>L6)
>
> Biff
>
> "pboost1" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hello everybody. I am trying to do something with a spreadsheet and not
>> sure how to work the formula. I have seven columns and seven rows. The
>> column starts at F and the rows start and 1. The formula I have is
>> this:
>>
>> =IF(SUM(K1:K5)>=30,SUM(K1:K5)-30,IF(SUM(K1:K5)>=20,SUM(K1:K5)-20,IF(SUM(K1:K5)>=10,SUM(K1:K5)-10,IF(SUM(K1:K5)<10,SUM(K1:K5)))))
>>
>>
>> This is in cell K6. I would like this formula to also be able to look
>> at cells L6 and L7, and if L7 is larger than L6, subtract 1 from result
>> of the formula in K6. Can something like this be done? Thank you in
>> advance for any help that can be given.
>>
>>
>> --
>> pboost1
>> ------------------------------------------------------------------------
>> pboost1's Profile:
>> http://www.excelforum.com/member.php...o&userid=11895
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=398300
>>
>
>

[pboost1]

I appreciate the help, thanks.