Forum Discussion
Mutiply formula with 3 or more criteria
Hi everyone!
I would need your help in teaching me how to obtain the formula that allows me to calculate the Risk Appetite based on several criteria and conditions.
There are 3-4 conditions which are
1. my GP% is >0 ≤ 7.5% and the contract value is between >0m ≤ 100m, the gross profit will multiply by 0.5%. But if the contract value is >100 ≤ 500m, the gross profit will mutiply by 0.25% etc etc
2. If the GP% > 9% and contract value is >0m ≤ 100m, the gross profit will multiply by 1%. But if the contract value is >100 ≤ 500m, the gross profit will mutiply by 0.8% etc etc
I tried with IF formula but i couldn't get it. Could you please help me? Thank you!
See the attached version.
10 Replies
Create a lookup table like this - I made up some numbers.
You can expand the table if needed.
Use INDEX/MATCH to look up the multiplication factor for a given GP and contract value.
=INDEX($G$3:$I$5, MATCH(A2, $F$3:$F$5), MATCH(B2, $G$2:$I$2))
HansVogelaar thank you so much Hans! if i have additional criteria, i will just need to expand the table?
Yes, indeed!
