 # Difficult excel formular with many factors

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)

Best

Ralf

4 Replies

# Re: Difficult excel formular with many factors

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

See the attached demo.

# Re: Difficult excel formular with many factors

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

# Re: Difficult excel formular with many factors

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

# Re: Difficult excel formular with many factors

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