IF function greater than 115 less than 195

[dbroderick]

I am trying to get my Total Days cell to show a formula that allows me leave out 115 in the YTD Balance column's bottom cell and cap the time to use at 195. I have tried numerous formulas all with false returns and would appreciate any help! Thank you!


The current formula is =((F137+F138+(F140+F141+F142))/8)+IF(F143<115, F143, F143-115)/8

I am using Excel 2010 with Windows XP.

[Dionysos]

Your attachment does not match your problem statement.

Here are my assumptions:
1. You want to subtract 115 from F143 as long as it doesn't go below 0, else use 0
2. You want to cap the total hours to 195
3. You are excluding F139 intentionally

Please Login or Register  to view this content.

[dbroderick]

I've not made myself very clear. Sorry about that. Not enough sleep!

That little snip is only a small part of a much larger spreadsheet. I only want to cap the number in F9 of my attachement. In order to be counted in the main formula, it must be greater than 115 and less than 195.

So my formula should add cells F3, 4, 6, 8 and then add F9 if greater than 115 and up to 195.

[Dionysos]

Assumptions:
1. Subtract 115 from F9 as long as it is not below 0, else use 0
2. After subtracting 115 from F9, cap at 195
3. Include F3-F9 in summation

Please Login or Register  to view this content.

[dbroderick]

Yes! That does the trick.

Thank you!!