Discussion Re: under, between, over in Excel
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2118959#M88850
<P><LI-USER uid="958538"></LI-USER> </P>
<P>Basically the same idea:</P>
<P> </P>
<P>=A1*IF(A1<10000, 1000, IF(A1<=12000, 2000, 3000))</P>Tue, 09 Feb 2021 12:19:09 GMTHans Vogelaar2021-02-09T12:19:09Zunder, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2117076#M88660
<P>how to use formulas/functions in excel if answer requires additional functions?</P><P>if answer is under 10 need to multiply by 1000 and multiply again using specified cell,</P><P>if answer is between 10/12 need to multiply by 2000 and multiply again using specified cell,</P><P>if answer is over 10 need to multiply by 3000 and multiply again using specified cell,</P>Sun, 07 Feb 2021 19:07:33 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2117076#M88660insight132021-02-07T19:07:33ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2117083#M88661
<P><LI-USER uid="958538"></LI-USER> </P>
<P>Shouldn't the last one be "if answer is over <STRONG>12</STRONG>"?</P>
<P>Let's say the input is in cell A1, and the multiplication factor in B1.</P>
<P>In another cell:</P>
<P>=A1*B1*IF(A1<10,1000,IF(A1<=12,2000,3000))</P>Sun, 07 Feb 2021 19:13:37 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2117083#M88661Hans Vogelaar2021-02-07T19:13:37ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2118643#M88826
yes, does this help...how to functions;<BR /><BR />if under 10000 multiply 1000 x A1,<BR />if between 10000 - 12000 multiply 2000 x A1,<BR />if over 12000 multiply 3000 x A1.Tue, 09 Feb 2021 03:18:18 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2118643#M88826insight132021-02-09T03:18:18ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2118959#M88850
<P><LI-USER uid="958538"></LI-USER> </P>
<P>Basically the same idea:</P>
<P> </P>
<P>=A1*IF(A1<10000, 1000, IF(A1<=12000, 2000, 3000))</P>Tue, 09 Feb 2021 12:19:09 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2118959#M88850Hans Vogelaar2021-02-09T12:19:09ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119273#M88892
<P><LI-USER uid="127945"></LI-USER> Thank you for your replies and help. I tried to modify to and received message "you've entered too many arguments for this function". You've been helpful because I had 3 different calculations, and you consolidated into 1! Allow me to be more specific and see if we can resolve.</P><P>the 3 calculations are all based on A1.</P><P>if A1 is under 10,000, multiply 1,000 by B1</P><P>if A1 is between 10,000 and 12,0000, multiply 2,000 by B1</P><P>if A1 is over12,000, multiply 3,000 by B1</P><P> </P><P>I hope this makes more sense. Thank you for you help!</P><P> </P><P> </P><P>=IF(M20<10000, E20*1000, IF(M20=10000, M20<=12000, E20*2000), IF(M20>12000, 3000*E20)))</P>Tue, 09 Feb 2021 17:36:30 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119273#M88892insight132021-02-09T17:36:30ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119409#M88901
<P><LI-USER uid="958538"></LI-USER> </P>
<P>Why did you add M20=10000?</P>
<P> </P>
<P>The formula can be simpler:</P>
<P> </P>
<P>=E20*IF(M2<10000, 1000, IF(M2<=12000, 2000, 3000))</P>Tue, 09 Feb 2021 19:12:41 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119409#M88901Hans Vogelaar2021-02-09T19:12:41ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119433#M88905
<P><LI-USER uid="958538"></LI-USER> </P>
<P>As variant</P>
<LI-CODE lang="excel">=E20*1000*LOOKUP(M2,{0,10000,11999.99}, {1,2,3})</LI-CODE>
<P> </P>Tue, 09 Feb 2021 19:39:52 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119433#M88905Sergei Baklan2021-02-09T19:39:52ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119716#M88949
<P><LI-USER uid="127945"></LI-USER> in the spreadsheet thats where it was located and I failed to change it to be more in line with your suggestion which was A1</P>Wed, 10 Feb 2021 01:24:50 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119716#M88949insight132021-02-10T01:24:50ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119718#M88950
how does changing the 11999.99 to 12000.00 have an impact?Wed, 10 Feb 2021 01:33:25 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119718#M88950insight132021-02-10T01:33:25ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119762#M88965
<P><LI-USER uid="958538"></LI-USER> </P><P>The formula should be constructed like this:</P><LI-CODE lang="excel">=E20*
LOOKUP(M20,{0,10000,12000.01},
{1000,2000,3000})</LI-CODE>Wed, 10 Feb 2021 04:06:48 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119762#M88965Twifoo2021-02-10T04:06:48ZRe: under, between, over
https://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119883#M88976
<P><LI-USER uid="280482"></LI-USER> </P>
<P>Thank you, my mistake - for the exactly twelve thousand multiplier is still two thousand.</P>Wed, 10 Feb 2021 08:19:55 GMThttps://techcommunity.microsoft.com/t5/excel/under-between-over/m-p/2119883#M88976Sergei Baklan2021-02-10T08:19:55Z