Help an amateur

[Truebluedan]

Morning

This is really stumping me and if possible could somebody help me the formula.I’ve attached a screenshot to make it easier. Each person in Column A is given a monetary amount based on what tier they work in (column B). Each tier has a value if target is achieved.

Tier 1 €50
Tier 2 €150
Tier 3 €225

Each person needs to hit a minimum in column c and d to receive the reward.

Column C target is greater than 16 whilst column D target is greater than 24.54%. Column E would then return the monetary value achieved. Anybody not hitting either of the targets would receive 0.

Can anybody help please?

Thanks

Dan

 

[JW00]

If your table structure is as below, you can use "=IF(AND(C2>16,D2>24.54%),B2,0)".

Date Exp.xlsb
	A	B	C	D	E
1	Name	Tier Value	Target 1	Target 2	Reward
2	Jack	€ 50	17	26.50%	€ 50
3	John	€ 150	11	33.00%	€ 0
4	Simon	€ 225	23	15.00%	€ 0
5	Dave	€ 225	33	29.00%	€ 225
6	Sarah	€ 150	13	26.00%	€ 0
7	Jane	€ 50	17	19.00%	€ 0
8	Jessica	€ 225	19	40.00%	€ 225
Sheet3

Cell Formulas
Range	Formula
E2:E8	E2	=IF(AND(C2>16,D2>24.54%),B2,0)

 

[Truebluedan]

If your table structure is as below, you can use "=IF(AND(C2>16,D2>24.54%),B2,0)".

Date Exp.xlsb
	A	B	C	D	E
1	Name	Tier Value	Target 1	Target 2	Reward
2	Jack	€ 50	17	26.50%	€ 50
3	John	€ 150	11	33.00%	€ 0
4	Simon	€ 225	23	15.00%	€ 0
5	Dave	€ 225	33	29.00%	€ 225
6	Sarah	€ 150	13	26.00%	€ 0
7	Jane	€ 50	17	19.00%	€ 0
8	Jessica	€ 225	19	40.00%	€ 225
Sheet3

Cell Formulas
Range	Formula
E2:E8	E2	=IF(AND(C2>16,D2>24.54%),B2,0)

Thanks JW00 really helpful

If the tiers in col B are just number for example it will be said person name In A then in B it would just be what tier they fall under not the value, how would I go about that?

 

[JW00]

This should work "=IF(AND(C2>16,D2>24.54%),IF(B2=1,50,IF(B2=2,150,225)),0)".

 

[Truebluedan]

Thanks massive help worked

 

[JW00]

No problem.