 # Formula with reference and "if"

I have a pretty simple sheet, which I would like to add a formula to, but I cannot find the right formula.

I would like the sheet to automatically give a discount on the customers order, which I fill manually. The discount gets higher, after how much they order. But the discount also gets higher if they make a longer commitment, fx two or three year deals.

I therefore have 9 automated discounts (see the box on the right)

I would like the box in the bottom to automatically select which discount to give.

The logical explanation for fx one year would be:

If the total amount is less than 50.000, choose discount 1, and if the total amount is between 50.000 and 100.000, choose discount 2, and if the total amount is above 100.000 choose discount 3.

But I can't seem to get it right. HELP PLEASE

3 Replies

# Re: Formula with reference and "if"

@Beginner1075

In B19:

=\$D\$16*(100%-INDEX(\$G\$5:\$I\$7,ROW(\$A19)-ROW(\$A\$19)+1,MATCH(\$D\$16,{0,50000,100000})))

Fill down to B21.

# Re: Formula with reference and "if"

check this and i hope it will helps you@Beginner1075

# Re: Formula with reference and "if"

@alexanderjaml Great, it worked!! Thank you so much for your help.