Sheffield Forum

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: 913
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,975
Status: Online
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: 55
=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: 913
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: 55
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: 913
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: 55
=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: 913
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: 55
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: 913
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
05-04-2017, 19:57   #11
walkerx
Registered User
 
Joined: Sep 2010
Total Posts: 1,219
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
_______
ISP: SKY|Sync: 70Mbit/20Mbit
Network: Sky Q Hub|pfSense
Mobile: Pixel XL 128GB|Nexus 6|Nexus 4|Google Pixel C
Media Devices: Sky Q 2TB + 2 minis|Xbox One|Shield Android TV
  Reply With Quote
06-04-2017, 10:13   #12
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 55
Quote:
Originally Posted by walkerx View Post
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?
  Reply With Quote
06-04-2017, 10:44   #13
walkerx
Registered User
 
Joined: Sep 2010
Total Posts: 1,219
Quote:
Originally Posted by Katie Event View Post
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?
Nope, it will add the time together and if you subtract it will subtract the time

A lot of people under estimate excel and what it can do. You can add/subtract time and dates with simple operands

if you have excel, give it a go and you'll be surprised with the results.
_______
ISP: SKY|Sync: 70Mbit/20Mbit
Network: Sky Q Hub|pfSense
Mobile: Pixel XL 128GB|Nexus 6|Nexus 4|Google Pixel C
Media Devices: Sky Q 2TB + 2 minis|Xbox One|Shield Android TV
  Reply With Quote
06-04-2017, 11:06   #14
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 55
https://postimg.org/image/8r3fompfr/

I get an Error in LibreOffice, which has the same function library.

The MS Excel help page says:
MIN(number1, [number2], ...)

The MIN function syntax has the following arguments:

Number1, number2, ... Number1 is optional, subsequent numbers are optional. 1 to 255 numbers for which you want to find the minimum value.
I don't see how it can compare the value of BŁ+C3 to 17:00 - the speech marks you've put around it mean Excel sees it as a piece of text.
  Reply With Quote
06-04-2017, 11:50   #15
walkerx
Registered User
 
Joined: Sep 2010
Total Posts: 1,219
Quote:
Originally Posted by Katie Event View Post
https://postimg.org/image/8r3fompfr/

I get an Error in LibreOffice, which has the same function library.

The MS Excel help page says:
MIN(number1, [number2], ...)

The MIN function syntax has the following arguments:

Number1, number2, ... Number1 is optional, subsequent numbers are optional. 1 to 255 numbers for which you want to find the minimum value.
I don't see how it can compare the value of BŁ+C3 to 17:00 - the speech marks you've put around it mean Excel sees it as a piece of text.
LibreOffice/OpenOffice are limited and do not offer the same functionality as Excel.

As I stated, use Excel

just done a google search which also shows that this can be done

goo.gl/UrFeRP
_______
ISP: SKY|Sync: 70Mbit/20Mbit
Network: Sky Q Hub|pfSense
Mobile: Pixel XL 128GB|Nexus 6|Nexus 4|Google Pixel C
Media Devices: Sky Q 2TB + 2 minis|Xbox One|Shield Android TV
  Reply With Quote
06-04-2017, 12:08   #16
altus
Registered User
 
Joined: Aug 2006
Location: Nether Edge
Total Posts: 3,975
Status: Online
Quote:
Originally Posted by walkerx View Post
Nope, it will add the time together and if you subtract it will subtract the time

A lot of people under estimate excel and what it can do. You can add/subtract time and dates with simple operands

if you have excel, give it a go and you'll be surprised with the results.
Excel and LibreOffice both store dates/times as numbers (1 day is 1 so 12 hours is 0.5). Whether they get displayed as days/times or as numbers is a formatting issue. Calculations on them operate on numbers.

Your formula is trying to find the minimum of a number and a string. As Katie says, LibreOffice reports an error - I strongly suspect Excel does too. You'd need to convert the time string to a number for it to work.

e.g. =MIN(B3+C3,TIMEVALUE("17:00"))

It may be that Excel notices that "17:00" contains a time in this circumstance and LibreOffice doesn't - they both handle ="15:32" - NOW() correctly.
_______
Help your doctor prescribe the right drugs - sign up to alltrials.net

Last edited by altus; 06-04-2017 at 12:17.
  Reply With Quote
06-04-2017, 21:50   #17
walkerx
Registered User
 
Joined: Sep 2010
Total Posts: 1,219
excel does not report an error

http://imgur.com/a/mIgvB

if anyone uses excel would they like to try the formula i have posted

or you could actually try excel online as microsoft provide basic usage of the app for free online
_______
ISP: SKY|Sync: 70Mbit/20Mbit
Network: Sky Q Hub|pfSense
Mobile: Pixel XL 128GB|Nexus 6|Nexus 4|Google Pixel C
Media Devices: Sky Q 2TB + 2 minis|Xbox One|Shield Android TV
  Reply With Quote
07-04-2017, 13:50   #18
sgtkate
Registered User
 
Joined: Oct 2006
Total Posts: 3,840
Quote:
Originally Posted by walkerx View Post
excel does not report an error

http://imgur.com/a/mIgvB

if anyone uses excel would they like to try the formula i have posted

or you could actually try excel online as microsoft provide basic usage of the app for free online
Yes, it works fine in Excel. It also correctly calcs the hours too, so if you write the query like:

=MIN(B3+C3,"27:00")

And have these values which add up to more than 24 hours:
B3 = 13:00
C3= 12:00

It give the correct response of 01:00 and not 25:00
  Reply With Quote
11-04-2017, 16:15   #19
Katie Event
Registered User
Katie Event's Avatar
 
Joined: Nov 2015
Total Posts: 55
I don't use Excel as it won't run on my operating system. I stand corrected as to the way the function works in Excel though - as altus says, eveiddently MS have written Excel so it recognises "17:00" as a time, which is useful to know. I haven't used Excel since about 2007, and it certainly didn't used to do so!

As for adding 12:00 to 13:00 and getting the answer 01:00 - I guess whether or not that is useful depends on whether you want the answer 25 hours or 1am!
  Reply With Quote
12-04-2017, 20:42   #20
iansheff
Registered User
iansheff's Avatar
 
Joined: Dec 2007
Total Posts: 5,596
Status: Online
Is it not something like equals or less than 15 that goes in the formula? I once did something similar years ago but cannot remember off hand apart from what I put above.
  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 18:40.
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