Forum Discussion

Copper Contributor

Feb 07, 2021

under, between, over

how to use formulas/functions in excel if answer requires additional functions? if answer is under 10 need to multiply by 1000 and multiply again using specified cell, if answer is between 10/12 ne...

Formulas and Functions

Copper Contributor

Feb 09, 2021

yes, does this help...how to functions;

if under 10000 multiply 1000 x A1,
if between 10000 - 12000 multiply 2000 x A1,
if over 12000 multiply 3000 x A1.

MVP

Feb 09, 2021

Basically the same idea:

=A1*IF(A1<10000, 1000, IF(A1<=12000, 2000, 3000))

insight13
Copper Contributor
Feb 09, 2021
HansVogelaar 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.
the 3 calculations are all based on A1.
if A1 is under 10,000, multiply 1,000 by B1
if A1 is between 10,000 and 12,0000, multiply 2,000 by B1
if A1 is over12,000, multiply 3,000 by B1

I hope this makes more sense. Thank you for you help!

=IF(M20<10000, E20*1000, IF(M20=10000, M20<=12000, E20*2000), IF(M20>12000, 3000*E20)))
- SergeiBaklan
  Diamond Contributor
  Feb 09, 2021
  insight13
  
  As variant
  
  =E20*1000*LOOKUP(M2,{0,10000,11999.99}, {1,2,3})
  - insight13
    Copper Contributor
    Feb 10, 2021
    how does changing the 11999.99 to 12000.00 have an impact?
- HansVogelaar
  MVP
  Feb 09, 2021
  insight13
  
  Why did you add M20=10000?
  
  The formula can be simpler:
  
  =E20*IF(M2<10000, 1000, IF(M2<=12000, 2000, 3000))
  - insight13
    Copper Contributor
    Feb 10, 2021
    HansVogelaar 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

Resources