Help with formula please.

[bionicle]

Hi everyone,

Can some one provide some guidance on a formula please. I have a drop down with "group1, group2, group3" in cell J1 and based on that value I need a formula that look at a salary in column J3 that will calculate 20% of that salary, if the sum of that formula is between £4,500 and £7500 then it does nothing but if its under or over it will default to either the £4500 or the £7500. the result needs to be in cell K3

Any help would be appreciated.

 

[Peter_SSs]

You have not explained what effect the different drop-down values have in relation to the calculation/result

 

[etaf]

Edit , as posted saw that Peter_SSs replied
so ignore

what does the dropdown do to the formula

=IF( AND( J3*1.2>=4500,J3*1.2<=7500),"", IF(J3*1.2<4500,4500,7500))

But i dont understand the groups

 

[Peter_SSs]

Something else that might influence the best method for you:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

Since I will be signing off for the night shortly I will take some punts that the group could affect the percentage calculation and the minimum and maximum results allowed.
Also assuming for now that Ms365 or higher is available.

22 03 03.xlsm
	J	K	L	M	N	O	P	Q
1	group2				Group	%	Min	Max
2					group1	20%	4,500	7,500
3	33000	7260			group2	22%	6,000	10,000
4					group3	25%	7,500	12,000
Sheet2

Cell Formulas
Range	Formula
K3	K3	=LET(g,FILTER(O2:Q4,N2:N4=J1),MIN(MAX(INDEX(g,1)*J3,INDEX(g,2)),INDEX(g,3)))

 

[bionicle]

Something else that might influence the best method for you:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Account details have been updated.

 

[bionicle]

You have not explained what effect the different drop-down values have in relation to the calculation/result

The three groups in the drop down are different % for different groups, thought it would be easier to ask about one formula and then adapting it to fit with the others.

 

[Peter_SSs]

Thanks for updating your details.

Then if the 4,500 and 7,500 min/max applies to all groups, try this structure

22 03 03.xlsm
	J	K	L	M	N	O
1	group2				Group	%
2					group1	20%
3	33000	7260			group2	22%
4					group3	25%
Sheet2

Cell Formulas
Range	Formula
K3	K3	=MIN(MAX(VLOOKUP(J1,N2:O4,2,0)*J3,4500),7500)

 

[bionicle]

Worked a treat, thank you ever so much

Thanks for updating your details.

Then if the 4,500 and 7,500 min/max applies to all groups, try this structure

22 03 03.xlsm
	J	K	L	M	N	O
1	group2				Group	%
2					group1	20%
3	33000	7260			group2	22%
4					group3	25%
Sheet2

Cell Formulas
Range	Formula
K3	K3	=MIN(MAX(VLOOKUP(J1,N2:O4,2,0)*J3,4500),7500)

 

[Peter_SSs]

You're welcome. Glad to help.