Grahamfff   10 #1 Posted March 20, 2017 In a spreadsheet I'm making, I'd like to be able to set a maximum value that a cell will return.  In this spreadsheet, cell A3 = A1 + A2  So if A1=5 and A2 = 6 then A3=11  But I'd like A3 never to exceed 15, so if A1=10 and A2 =6, I'd like the value in A3 to be 15 and not 16 - in fact whatever the value of A1 and A2, A3 should never exceed 15.  Is there a formula I can enter in A3 to ensure this happens?  Thanks Share this post Link to post Share on other sites Share this content via...
altus   540 #2 Posted March 20, 2017 Off the top of my head, something like:  =MIN(a1+a2, 15) Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #3 Posted March 20, 2017 =if(a1+a2<=15, a1+a2, 15)  EDIT - which 'reads' if a1+a2 is less than or equal to 15, put in the value of a1+a2, otherwise put in 15 Share this post Link to post Share on other sites Share this content via...
Grahamfff   10 #4 Posted March 20, 2017 Perfect! Thanks very much!  Off the top of my head, something like: =MIN(a1+a2, 15) Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #5 Posted March 20, 2017 I've never used MIN() before. That's a neat way of doing it, Altus. Share this post Link to post Share on other sites Share this content via...
Grahamfff   10 #6 Posted March 21, 2017 Do you think this can be made to work where the values being worked with are times not ordinary numbers?  So if A1 = 9am and A2 = 5pm then A3=8:00 (the difference between the two)  But I'd like A3 to show a maximum of 6 hours  I've tried =Min(A2-A1,6) - this doesn't throw up an error but it doesn't give me the right result either!    Perfect! Thanks very much! Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #7 Posted March 21, 2017 (edited) =MIN(HOUR(a1-a2), 6)  Make sure cell is formatted as numbers, not time.  ---------- Post added 21-03-2017 at 21:22 ----------  I was thinking about this on the ride home. The above will only work for whole hours.  When calculating in hours and minutes, Excel uses decimal fractions of the day - so 1 hour is 0.41666666. When you format that value as Time (Format/Cells) it is displayed as 1:00 - but the *value* is 0.4166666.  6 hours is a quarter of a day - or 0.25, so  =MIN(B1-A1,0.25)  will work for all times in the same day, but you need to make sure the answer cell is formatted as time.  Because of the fact that Excel calculates time as a fraction of a day that it didn't work when you were doing it - one time minus another can only range between -1 and 1 (rounded) hence it is important to format as time, and hence why the '6' value in your formula wasn't apparently doing anything.  If you are going to use it in a further calculation (for example you want to calculate wages based on the time by multiplying by an hourly rate) you will need to multiply by 24 because the value underlying the time display is a fraction of a day.  It won't cope with time periods spanning midnight though - if you want to do that you will need to enter a time and a date, so Excel knows, for example, that if A1 has 23:00 in it and A2 has 02:00 in it, that the 2am time is 3 hours later than A1, not 21 hours earlier. Edited March 21, 2017 by Katie Event Share this post Link to post Share on other sites Share this content via...
Grahamfff   10 #8 Posted March 22, 2017 Thanks very much Katie - I'll give this a try shortly.  =MIN(HOUR(a1-a2), 6) Make sure cell is formatted as numbers, not time.  ---------- Post added 21-03-2017 at 21:22 ----------  I was thinking about this on the ride home. The above will only work for whole hours.  When calculating in hours and minutes, Excel uses decimal fractions of the day - so 1 hour is 0.41666666. When you format that value as Time (Format/Cells) it is displayed as 1:00 - but the *value* is 0.4166666.  6 hours is a quarter of a day - or 0.25, so  =MIN(B1-A1,0.25)  will work for all times in the same day, but you need to make sure the answer cell is formatted as time.  Because of the fact that Excel calculates time as a fraction of a day that it didn't work when you were doing it - one time minus another can only range between -1 and 1 (rounded) hence it is important to format as time, and hence why the '6' value in your formula wasn't apparently doing anything.  If you are going to use it in a further calculation (for example you want to calculate wages based on the time by multiplying by an hourly rate) you will need to multiply by 24 because the value underlying the time display is a fraction of a day.  It won't cope with time periods spanning midnight though - if you want to do that you will need to enter a time and a date, so Excel knows, for example, that if A1 has 23:00 in it and A2 has 02:00 in it, that the 2am time is 3 hours later than A1, not 21 hours earlier. Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #9 Posted March 23, 2017 How did you get on Grahamfff? Share this post Link to post Share on other sites Share this content via...
Grahamfff   10 #10 Posted March 23, 2017 Sorry - not go too it yet - I'll definitely get back to you when I've done so.  How did you get on Grahamfff? Share this post Link to post Share on other sites Share this content via...
walkerx   10 #11 Posted April 5, 2017 if doing time calculations just do the following  B3 = 05:00 C3 = 07:00  =MIN(B3+C3,"17:00")  This will show 12:00 as B3+C3 = 12:00  If changed the values so higher then it will show 17:00 Share this post Link to post Share on other sites Share this content via...
Katie Event   10 #12 Posted April 6, 2017 if doing time calculations just do the following B3 = 05:00 C3 = 07:00  =MIN(B3+C3,"17:00")  This will show 12:00 as B3+C3 = 12:00  If changed the values so higher then it will show 17:00  That will attempt to display the smallest value out of the value in B3, the value in C3 or the text string 17:00 - the latter doesn't have a numerical value, so the formula won't work, surely? Share this post Link to post Share on other sites Share this content via...