PDA

View Full Version : AutoSum >40



sclarady
01-25-2014, 12:17 AM
I want 7 cells to be added up and placed in a new cell. If that sum is more then 40 all I want in that sell is 40 and the remainder to be placed in the next cell. How do I do this?

mancubus
01-25-2014, 05:59 AM
in J5:

=IF(SUM(C5:I5)>40,40,SUM(C5:I5))

in K5:

=IF(SUM(C5:I5)>40,SUM(C5:I5)-40,0)


copy down to desired row.

sclarady
01-25-2014, 11:38 AM
Thank You. I did try the =IF but some people at work said that is not what I needed so I did not work with it much. I almost had it too. I had =IF(C5:I5)>40,J5=40. Thanks again.





in J5:

=IF(SUM(C5:I5)>40,40,SUM(C5:I5))

in K5:

=IF(SUM(C5:I5)>40,SUM(C5:I5)-40,0)


copy down to desired row.

mancubus
01-25-2014, 12:48 PM
you are welcome.

Aussiebear
01-25-2014, 06:16 PM
I did try the =IF but some people at work said that is not what I needed so I did not work with it much.

What alternative did they suggest?

Bob Phillips
01-26-2014, 03:40 PM
Look, no IFs

=MIN(40,SUM(C5:I5))

=MAX(0,SUM(C5:I5)-40)

mancubus
01-27-2014, 05:31 AM
Look, no IFs=MIN(40,SUM(C5:I5))=MAX(0,SUM(C5:I5)-40)thanks xld.