Sheffield Forum
Your message here

Excel query - formula for setting a maximum value

Home > General > Computer & Tech Chat

Reply To Topic
 
Thread Tools Search this Thread
20-03-2017, 15:54   #1
Grahamfff
Registered User
 
Joined: Apr 2005
Location: Wadsley
Total Posts: 910
Send a message via MSN to Grahamfff
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
  Reply With Quote
20-03-2017, 16:10   #2
altus
Registered User
 
Joined: Aug 2006
Location: Nether Edge
Total Posts: 3,945
Off the top of my head, something like:

=MIN(a1+a2, 15)
_______
Help your doctor prescribe the right drugs - sign up to alltrials.net
  Reply With Quote
20-03-2017, 16:20   #3
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 50
=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
  Reply With Quote
20-03-2017, 16:20   #4
Grahamfff
Registered User
 
Joined: Apr 2005
Location: Wadsley
Total Posts: 910
Send a message via MSN to Grahamfff
Perfect! Thanks very much!

Quote:
Originally Posted by altus View Post
Off the top of my head, something like:

=MIN(a1+a2, 15)
  Reply With Quote
20-03-2017, 17:41   #5
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 50
I've never used MIN() before. That's a neat way of doing it, Altus.
  Reply With Quote
21-03-2017, 16:00   #6
Grahamfff
Registered User
 
Joined: Apr 2005
Location: Wadsley
Total Posts: 910
Send a message via MSN to Grahamfff
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!



Quote:
Originally Posted by Grahamfff View Post
Perfect! Thanks very much!
  Reply With Quote
21-03-2017, 16:05   #7
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 50
=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.

Last edited by Katie Event; 21-03-2017 at 21:25.
  Reply With Quote
22-03-2017, 08:15   #8
Grahamfff
Registered User
 
Joined: Apr 2005
Location: Wadsley
Total Posts: 910
Send a message via MSN to Grahamfff
Thanks very much Katie - I'll give this a try shortly.

Quote:
Originally Posted by Katie Event View Post
=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.
  Reply With Quote
23-03-2017, 11:00   #9
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 50
How did you get on Grahamfff?
  Reply With Quote
23-03-2017, 22:08   #10
Grahamfff
Registered User
 
Joined: Apr 2005
Location: Wadsley
Total Posts: 910
Send a message via MSN to Grahamfff
Sorry - not go too it yet - I'll definitely get back to you when I've done so.

Quote:
Originally Posted by Katie Event View Post
How did you get on Grahamfff?
  Reply With Quote
Reply To Topic

Thread Tools Search this Thread
Search this Thread:

Advanced Search



All times are GMT +1. The time now is 07:48.
POSTS ON THIS FORUM ARE NOT ACTIVELY MONITORED
Click "Report Post" under any post which may breach our terms of use.
©2002-2014 Sheffield Forum | Powered by vBulletin ©2017