Difficult excel formular with many factors

[Ralfi350]

Hi all,

it's the first time here for getting your support and really hope to get an answer which brings me forward.

The task is to get a result which is influenced of following data:

A sales manager (SM) gets a bonus based on a sales contract value as long as this value match the standard price list. If the value is higher than the basic value than it rates with a factor.

i.e.

a) Contract Value = 10.000 (A1) and standard price list = 10.000 (A2) then factor is 1,0 and result need to be shown in (A3) 

b) Contract Value (A1) is >1,02 and <1,04 of standard price list (A2) then factor is 1,2; or >1,04 and <1,05 the factor is 1,5; or >1,06  and <1,08 the factor is 1,8; or =/>1,08 the factor is 2,0 and result need to be also shown in (A3)

Anybody with an idea? Thanks for thinking about and comments.

Best 

Ralf

[#2]

@Ralfi350 

I'd use VLOOKUP (or XLOOKUP) in combination with a lookup table.

See the attached demo.

[#3]

@Hans Vogelaar 

Hi Hans,

thanks for your comment and proposal. The numbers 1,03 / 1,05 ... I can add in between to get the result which is fine.

Do you know how I can solve the problem for the last bonus step as here I have to count 1,08 and any higher value, means = and > of 1,08?

Best

Ralf

[#4]

@Ralfi350 

The formula should already handle that. Have you tried it?

[#5]

I have adjusted it to be on the save side with another line and >1,08 and yes everything works perfectly. Thank you so much for your support. have a nice evening. Best Ralf