|
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 |
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 |
Quote:
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)) ) |
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com