If conditions based on the inputs to one of the two columns

Hi Everyone,

I am stuck in working on the IF function (I don't know if any other formulas help).

I have two columns which is Euro and Dollar which need inputs, but it cannot appear at the same time. The next column, I want to calculate the bonuses based on the input to either Euro or Dollar. The conditions are different.

Condition to Euro: 0<=9.9 get 1 bonus, 10<=50.9 get 2 bonus, 60<=100 get 3. Max is 100
Condition to Dollar: 0<=9.9 get 1.5 bonus, 10<=50.9 get 2.5 bonus, 60<=100 get 3.5. Max is 100.

I also attached the sample sheet. Appreciate your help.

Kind regards,
Sarin

Try this in F4 and drag down

Please Login or Register  to view this content.

That is because you have not mentioned what value you want when it is 100 in the main Post..

So I designed the formula accordingly..

What do you want when it is 100?

Just change 100 to 101 like this {0;10;60;101} if you looking at max 100?

Guess you already have your answer..

Glad to have helped!

Basically I am trying to find a number lesser than 10^99 (A larger number than I expect) using Match, which returns me the Position. 1 (Euro) or 2 (Dollar).

Based on this output the further calculation happens..