Feb 07 2021 11:07 AM
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 need to multiply by 2000 and multiply again using specified cell,
if answer is over 10 need to multiply by 3000 and multiply again using specified cell,
Feb 07 2021 11:13 AM
Shouldn't the last one be "if answer is over 12"?
Let's say the input is in cell A1, and the multiplication factor in B1.
In another cell:
=A1*B1*IF(A1<10,1000,IF(A1<=12,2000,3000))
Feb 08 2021 07:18 PM
Feb 09 2021 04:19 AM
Feb 09 2021 09:36 AM
@Hans Vogelaar 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)))
Feb 09 2021 11:12 AM
Why did you add M20=10000?
The formula can be simpler:
=E20*IF(M2<10000, 1000, IF(M2<=12000, 2000, 3000))
Feb 09 2021 11:39 AM
Feb 09 2021 05:24 PM
@Hans Vogelaar 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
Feb 09 2021 05:33 PM
Feb 09 2021 08:06 PM
The formula should be constructed like this:
=E20*
LOOKUP(M20,{0,10000,12000.01},
{1000,2000,3000})
Feb 10 2021 12:19 AM
Thank you, my mistake - for the exactly twelve thousand multiplier is still two thousand.