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.

 

[bionicle]

Hi all,

The formula works great however I've hit another block.

I'm now trying to put together 3 formulas into one, could someone help with this and advise on how this would work please.

=(MIN(MAX(VLOOKUP(N21,R21:S24,2,0)*N22,4608),7097))

=(MIN(MAX(VLOOKUP(N21,R21:S24,2,0)*N22,3898),4967))

=(MIN(MAX(VLOOKUP(N21,R21:S24,2,0)*N22,1066),1845))

 

[Peter_SSs]

Looks like you need to have another look at post #5

 

[bionicle]

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)))

Tried post 5 and all i get in the cell is #NAME?, any ideas why?

 

[Fluff]

Do you have the LET function?

 

[bionicle]

Do you have the LET function?

Just looked and notice I don't have the LET function available?

 

[Fluff]

If you type =Le into a cell is one of the options LET?

 

[bionicle]

If you type =Le into a cell is one of the options LET?

That's how I found out I don't have it , just checked online and according to the MS website I have to have version 2009 or later and I'm running version 2008,

I don't know whether there is another way to get the LET function or whether the formula can be wrote using an alternative method.

 

[Fluff]

It can be done without let, like

=MIN(MAX(FILTER(O2:O4,N2:N4=J1)*J3,FILTER(P2:P4,N2:N4=J1)),FILTER(Q2:Q4,N2:N4=J1))

 

[bionicle]

It can be done without let, like

=MIN(MAX(FILTER(O2:O4,N2:N4=J1)*J3,FILTER(P2:P4,N2:N4=J1)),FILTER(Q2:Q4,N2:N4=J1))

That worked a treat, thank you.

Just need to speak with our IT department to see if I can get an upgrade.

 

[Fluff]

Glad we could help & thanks for the feedback.