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

Is this it?

=IF(L7L6,(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))))))


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398300

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)))))-(L7L6)

Biff

"pboost1" wrote in
message ...

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

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

if (L7L6,your formula -1, your formula)

=IF(L7L6,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

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}))-(L7L6)

Biff

"Biff" wrote in message
...
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)))))-(L7L6)

Biff

"pboost1" wrote in
message ...

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

I appreciate the help, thanks.


--
pboost1
------------------------------------------------------------------------
pboost1's Profile: http://www.excelforum.com/member.php...o&userid=11895
View this thread: http://www.excelforum.com/showthread...hreadid=398300