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?
---------- 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.
Thanks very much Katie - I'll give this a try shortly.
Quote:
Originally Posted by Katie Event
=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.