 |
|
| TheExcelAddict.com | |
| December 5, 2018 | |
| Hi fellow Excel Addict, | |
We're
expecting our first winter storm of the season
Thursday night. The forecast is saying 15 to 30
centimeters (6-12 inches) of snow. That's not
exactly how I would like to 'ease' into winter. But
I'm ready for it. Today, my 'Excel in Seconds' tip 'Use MAX Function To Get Minimum Value' may seem a little counter-intuitive. I always appreciate it when you share my tips so, if you find this tip helpful, please share is with other Excel users that you know. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
 |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
| Use MAX Function To
Get Minimum Value |
|
When need to limit the result of your formula to a specific minimum value, you may be surprised to discover that you need to use the MAX function. Last month, Wendy wrote me to ask... Hi Francis,
Here's my current cell
formula: =ROUND((B4+C4)*0.05,0)
I want to apply a minimum of $20, so if the result of the formula is less than $20, it will show $20 instead of the formula result. Is there a way to do this? The solution to this is simple but not obvious. You need to use the MAX function to apply a minimum value. =MAX(value1,
[value2], [value3], ...)
When you supply multiple values to the MAX function it returns the maximum of those values. By supplying two values to this function, that is, your formula result and your desired minimum value, whenever the formula result is less than the minimum value, the minimum value will be returned. To make this work with any formula, you simply wrap the MAX function and your desired minimum value around your original formula. =MAX(original_formula,
desired_minimum_value)
In Wendy's case, the formula was... =MAX(ROUND((B4+C4)*0.05,0),20)
 As you can see, where the formula results were less than 20, the MAX function returned the maximum of the two values. Use the MIN Function To Force A Maximum Value As you may have surmised by now, to limit the result of a formula to a specific maximum value, you can use the MIN function in this same way.  |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |