Excel query - formula for setting a maximum value

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

Off the top of my head, something like:

=MIN(a1+a2, 15)

=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

Perfect! Thanks very much!

Off the top of my head, something like:

 

=MIN(a1+a2, 15)

I've never used MIN() before. That's a neat way of doing it, Altus.

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!

=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.

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.

How did you get on Grahamfff?

Sorry - not go too it yet - I'll definitely get back to you when I've done so.

How did you get on Grahamfff?

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

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?