Using Multiple if functions

[LS019]

Good Afternoon!

I'm stuck, please help. (FYI, this is my first time ever using a help thread, please let me know what I can do better in order to communicate my issue - Thank you!)

Here is what I am trying to accomplish:

If F2 = 5 then I need to multiple M2 * 1
If F2 = 10 then I need to multiple M2 * 2
If f2 = 11-20 then I need to multiple M2 * 3
If F2 > 25 then I need to multiple M2 * 4

[TABLE="width: 500"]
<tbody>[TR]
[TD]F2[/TD]
[TD]M2[/TD]
[TD]=if([/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Welcome to the board.

This should be pretty easy with a lookup instead of nesting ifs.
But your ranges aren't very clear.
You specify 5 or 10, but what about 6 7 8 and 9?
Then also 21 to 25
What about less than 5 ?

Can you clarify that some more?

 

[Jonmo1]

I would think something like

=M2&MATCH(F2,{5,10,11,26})

 

[LS019]

I would think something like

=M2&MATCH(F2,{5,10,11,26})

Hi Jonmo1,
Thank you so much!

To clarify:

F2 = a rebate amount (5, 10, 11-20 includes rebates that are 15 and 20, and 25 or greater is jic the rebate is 25, 30, 35, etc) the rebates are traditionally issued in the dollars amounts of 5, 10, 15, 20, 25.

M2 = the number of items

*1, *2, *3, *4 = the dollar amount the number of items gets multiplied by based on the rebate amount.

The column I am trying to calculate will be the commission the sales person gets based on the rebate amount * the number of items sold - I really hope I am making sense.

=M2&MATCH(F2,{5,10,11,26})[/QUOTE] -- I am unfamiliar with this -- I will need to learn how it works.

 

[Jonmo1]

That should work, though I should have put * instead of &..

And the 26 should be 25 because you meant if it's greater than OR equal to 25, not just greater than.

So try
=M2*MATCH(F2,{5,10,11,25})

 

[LS019]

That should work, though I should have put * instead of &..

And the 26 should be 25 because you meant if it's greater than OR equal to 25, not just greater than.

So try
=M2*MATCH(F2,{5,10,11,25})

Hi Jonmo1 -- thank you again!
Where does the calculation take place? Is there a second part I need to add?
if F2 matches 5 then M2 *1
if F2 matches 10 then M2*2
if F2 matches 11 then M2*3
if F2 matches 26 then M2*4

 

[Jonmo1]

The match function is returning the position # of the largest number in the array {5,10,11,25} that is less than or equal to F5.

So if F5 is say 11, then the largest number in that array that is less or equal to 11 is 11, and it's in the 3rd position
So Match returns 3.

 

[LS019]

You lost me -- I'm going to look up the Match Function and learn about it, I bet everything you said above will make more sense. Thank you again for all your help.

 

[Jonmo1]

You're welcome.

Did the formula work for your needs?