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.
Last edited by dbroderick; 11-23-2011 at 12:33 PM. Reason: Remove craptastic table and upload spreadsheet.
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
=IF(F137+F138+F140+F141+F142+IF(F143-115<0,0,F143-115)>195,195/8,(F137+F138+F140+F141+F142+IF(F143-115<0,0,F143-115))/8)
Last edited by Dionysos; 11-23-2011 at 11:13 AM.
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.
Last edited by dbroderick; 11-23-2011 at 12:07 PM. Reason: More clarity.
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
=(F3+F4+F5+F6+F7+F8+IF(F9-115>195,195,IF(F9-115<0,0,F9-115)))/8
Yes! That does the trick.
Thank you!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks