How to find discount-% from target table?

[kingtoni]

What is the easiest way to find out how much I'll get discount:

Target value and Discount-%
50 000 0,50 %
100 000 1,00 %
150 000 1,50 %
200 000 2,00 %
250 000 2,50 %
300 000 3,00 %
350 000 3,50 %
400 000 4,00 %
450 000 4,50 %
500 000 5,00 %

My actual value is 225 000 and table above shows that I'll get 2,00% discount.

I tried to create function as below. It only shows which row you'll find your discount, not the actual percentage.


Public Function FindDiscount(ActualValue As Long, TargetValues As Range) As Long

Dim discount As Long

For Each TargetValues In Selection
If TargetValues <= ActualValue Then
discount = discount + 1
End If
Next

FindDiscount = discount

End Function


In function window my function show the correct row number but when I continue I'll get an error message about circular reference. Any idea..?

 

[anandvarma]

watch the following video in Youtube Channel by Mr. Mike Girvin

http://www.youtube.com/ExcelIsFun#p/u/1/lNiXtfJ6pJw

 

[Aladin Akyurek]

Do you really need VBA here for the following with E2 = 225000...

=LOOKUP(E2,Tvalues,Dpercentages)

would be lightning fast?

 

[kingtoni]

Oh darn... Thank you Aladin, that one worked just fine.

 

[Aladin Akyurek]

Oh darn... Thank you Aladin, that one worked just fine.

You are welcome. Thanks for providing feedback.