Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 24th 16, 03:06 PM
DUV DUV is offline
Junior Member
 
First recorded activity by ExcelBanter: Jun 2016
Posts: 3
Default combining MIN and MAX formulas...

I have a situation where i need the result to be constrained between two numbers . I can do the bottom and top constraints individually but cannot figure out how to combine the formulas. The formulas i have are...

Bottom =MAX(350,(A1*0.15%))
Top =MIN(1500,(A1*0.15%))

essentailly i want the result to be constrained between 350 and 1500 when multiplying cell A1 by 0.15%.

Any suggestions?

thanks in advance

  #2   Report Post  
Old June 24th 16, 04:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default combining MIN and MAX formulas...

I have a situation where i need the result to be constrained between
two numbers . I can do the bottom and top constraints individually
but cannot figure out how to combine the formulas. The formulas i
have are...

Bottom =MAX(350,(A1*0.15%))
Top =MIN(1500,(A1*0.15%))

essentailly i want the result to be constrained between 350 and 1500
when multiplying cell A1 by 0.15%.

Any suggestions?

thanks in advance


Try...

=IF(AND((A1*0.15%)350,(A1*0.15%)<1500),A1*0.15%,0 )

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #3   Report Post  
Old June 24th 16, 04:55 PM
DUV DUV is offline
Junior Member
 
First recorded activity by ExcelBanter: Jun 2016
Posts: 3
Default

Quote:
Originally Posted by GS[_6_] View Post
I have a situation where i need the result to be constrained between
two numbers . I can do the bottom and top constraints individually
but cannot figure out how to combine the formulas. The formulas i
have are...

Bottom =MAX(350,(A1*0.15%))
Top =MIN(1500,(A1*0.15%))

essentailly i want the result to be constrained between 350 and 1500
when multiplying cell A1 by 0.15%.

Any suggestions?

thanks in advance


Try...

=IF(AND((A1*0.15%)350,(A1*0.15%)<1500),A1*0.15%,0 )

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


Thanks for the suggestion, it doesn't quite do it but I used the suggestion as a base and figured it out - might not be the shortest solution but it does exactly what is needed...

=IF(AND(A1*0.15%<=350),350,IF(AND(A1*0.15%350,A1* 0.15%<1500),A1*0.15%,IF(AND(A1*0.15%=1500),1500)) )
  #4   Report Post  
Old June 24th 16, 06:35 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default combining MIN and MAX formulas...

'GS[_6_ Wrote:
;1623874'] I have a situation where i need the result to be
constrained between -
two numbers . I can do the bottom and top constraints individually
but cannot figure out how to combine the formulas. The formulas i
have are...

Bottom =MAX(350,(A1*0.15%))
Top =MIN(1500,(A1*0.15%))

essentailly i want the result to be constrained between 350 and
1500 when multiplying cell A1 by 0.15%.

Any suggestions?

thanks in advance-


Try...

=IF(AND((A1*0.15%)350,(A1*0.15%)<1500),A1*0.15%,0 )

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus




Thanks for the suggestion, it doesn't quite do it but I used the
suggestion as a base and figured it out - might not be the shortest
solution but it does exactly what is needed...

=IF(AND(A1*0.15%<=350),350,IF(AND(A1*0.15%350,A1* 0.15%<1500),A1*0.15%,IF(AND(A1*0.15%=1500),1500)) )


Glad it helped, and you were able to modify it to match your criteria!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #5   Report Post  
Old June 24th 16, 07:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,853
Default combining MIN and MAX formulas...

Hi,

Am Fri, 24 Jun 2016 16:55:59 +0100 schrieb DUV:

=IF(AND(A1*0.15%<=350),350,IF(AND(A1*0.15%350,A1* 0.15%<1500),A1*0.15%,IF(AND(A1*0.15%=1500),1500)) )


try:
=MAX(MIN(A1*0.15%,1500),350)


Regards
Claus B.
--
Windows10
Office 2016


  #6   Report Post  
Old June 24th 16, 11:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,182
Default combining MIN and MAX formulas...

Hi,

Am Fri, 24 Jun 2016 16:55:59 +0100 schrieb DUV:

=IF(AND(A1*0.15%<=350),350,IF(AND(A1*0.15%350,A1* 0.15%<1500),A1*0.15%,IF(AND(A1*0.15%=1500),1500)) )


try:
=MAX(MIN(A1*0.15%,1500),350)


Regards
Claus B.


Wizard!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
combining formulas benny29 Excel Worksheet Functions 0 January 23rd 09 03:14 PM
Combining formulas Shae Excel Worksheet Functions 5 April 15th 08 10:10 PM
Combining 2 Formulas Joker Excel Discussion (Misc queries) 1 February 1st 07 03:30 PM
combining formulas Sherri Excel Discussion (Misc queries) 3 July 19th 06 10:29 PM
Combining formulas jezzica85 Excel Discussion (Misc queries) 5 March 3rd 06 06:56 AM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017