Simplifying long 'MAX' formula

[koleyg]

Hi, New here and my experience level with excel is only a little above basic. using Excel 2010 and trying to mock up a time sheet template; have got most formulas in place however need a bit of help with simplifying the formula below

=(MAX(H14,0)+MAX(H15,0)+MAX(H16,0)+MAX(H19,0)+MAX(H20,0)+MAX(H22,0)+MAX(H23,0)+MAX(H24,0)+MAX(H27,0)+MAX(H28,0)) ... SUM (H14,H15 etc.) cells also custom formatted to show #,##0.00;;

what i need it to do is total (sum) the cells, but not show any of the negatives, in a less bulky formula if at all possible.

Any help would be appreciated.

cheers
Koley

 

[Shadow123]

When you say not show the negatives.. you mean you only want to add postive values, or you just don't want the result to show if the total is a negative?

 

[koleyg]

I have other formulas that feed into that cell; that when say: C14 : F14 are empty, H14 = '-0.40' (to adjust for a 7.60 hr day/time as lunch break is a preset value of 24 mins) ... this negative value does not show but is taken into account when adding the total as over a two week period it takes -4.0 hrs off the total ... if I'm making any sense? Don't know how else to explain it sorry.

So i need it to: Add up the positives - take the negatives and hide the value in H-Cells if they are the negative '-0.40'

I hope that's understandable. :S

 

[Teeroy]

Are the spaces in the range (such as H17, H18 etc.) empty or do you specifically need to exclude them? If they are just empty you could us the formula =sumif(H14:H28,">0").

 

[Shadow123]

if H14 is a negative, and you sum it... it will subtracted from the total. You can stop negative from showing up like this.... =if(sum(H14:H20)<0,"",sum(H14:H20))

 

[koleyg]

Thanks Shadow123 worked wonderfully

@ Teeroy all cells between H14-H27 have values in them

Thank you both .... honestly I'm a bit out of my depth working with formulas and I really appreciate the help!! Cheers !

 

[koleyg]

sorry back to it ... just did a manual working out of the hours and its not working as i thought. it's taking the negatives from all cells but i don't want it to necessarily.

this: G14=SUM(D14-C14)+(F14-E14) gives me 'total hours worked - less the lunch break' in HH:mm
where D14&E14 are the lunch break times ie 12:00 - 12:24 ... & ... C14&F14 are the start & finish times ie 7:00 - 16:00

this: H14=G14*(24)+IF(I14="PH","0.40") converts the HH:mm's to equivalent decimal and if C14 & F14 are 'empty' then H14="-0.40" though is not shown in the cell.

these formulas run through/across range C14-H28, the previous MAX formula I posted allowed for the -0.40 in H and didn't subtract it from the SUM total but only added positives

gawd I hope that makes sense?