How to calculate variable amount based on total entered manually

[mart_mrexcel]

• End user is giving a manual Total Salary, then
• The “Accommodation”, “Car Allowance” and “Basic” should automatically calculate based on the given formula as displayed in D3,E3,F4. How can i satisfy the formula equal to the variable that is entered manuall?

Book1
	B	C	D	E	F
2
3		This is Manual Entry ¤	25% Of the Basic min 3,333 ¤	10% of the Basic Max 1,500 ¤	This is the Remaing Total of "Total Salary" minus the Accomodation & Car Allowance ¤
4	Worker	Total Salary	Accomodation	Car Allow	Basic
5	Emp 1	22,000.00
Sheet1

 

[hrayani]

Hello,

Book2
	A	B	C	D	E
3		This is Manual Entry ¤	25% Of the Basic min 3,333 ¤	10% of the Basic Max 1,500 ¤	This is the Remaing Total of "Total Salary" minus the Accomodation & Car Allowance ¤
4	Worker	Total Salary	Accomodation	Car Allow	Basic
5	Emp 1	22000	4074.1	1629.6	16296.3
Sheet1

Cell Formulas
Range	Formula
C5	C5	=E5*25%
D5	D5	=E5*10%
E5	E5	=B5/1.35

 

[mart_mrexcel]

Hello,

Book2
	A	B	C	D	E
3		This is Manual Entry ¤	25% Of the Basic min 3,333 ¤	10% of the Basic Max 1,500 ¤	This is the Remaing Total of "Total Salary" minus the Accomodation & Car Allowance ¤
4	Worker	Total Salary	Accomodation	Car Allow	Basic
5	Emp 1	22000	4074.1	1629.6	16296.3
Sheet1

Cell Formulas
Range	Formula
C5	C5	=E5*25%
D5	D5	=E5*10%
E5	E5	=B5/1.35

Thank You Hrayani, what about the Max and minimum, the transpo has a max of 1500 and that difference should be added to the basic. The same thing with Accomodation if the minimum is applied it should be deducted from the Basic. When i added it, i had circular reference message.

 

[kevin9999]

This might be closer, although I confess I couldn't get it to work out 100%

Book1
	B	C	D	E	F
2
3		This is Manual Entry ¤	25% Of the Basic min 3,333 ¤	10% of the Basic Max 1,500 ¤	This is the Remaing Total of "Total Salary" minus the Accomodation & Car Allowance ¤
4	Worker	Total Salary	Accomodation	Car Allow	Basic
5	Emp 1	22,000.00	4,100.00	1,500.00	16,400.00
Sheet1

Cell Formulas
Range	Formula
D5	D5	=IF($C$5<=17999,3333,IF($C$5>=20250,($C$5-1500)/1.25*0.25,($C$5/1.35)*0.25))
E5	E5	=IF(((C5-D5)/1.1*0.1)>1500,1500,((C5-D5)/1.1*0.1))
F5	F5	=C5-SUM(D5:E5)

 

[hrayani]

Thank You Hrayani, what about the Max and minimum, the transpo has a max of 1500 and that difference should be added to the basic. The same thing with Accomodation if the minimum is applied it should be deducted from the Basic. When i added it, i had circular reference message.

You're welcome!
Yes you would get circular reference & that's obvious
If you add the difference to the basic salary then Accomodation will not be equal to 25% of the basic salary

 

[mart_mrexcel]

This might be closer, although I confess I couldn't get it to work out 100%

Book1
	B	C	D	E	F
2
3		This is Manual Entry ¤	25% Of the Basic min 3,333 ¤	10% of the Basic Max 1,500 ¤	This is the Remaing Total of "Total Salary" minus the Accomodation & Car Allowance ¤
4	Worker	Total Salary	Accomodation	Car Allow	Basic
5	Emp 1	22,000.00	4,100.00	1,500.00	16,400.00
Sheet1

Cell Formulas
Range	Formula
D5	D5	=IF($C$5<=17999,3333,IF($C$5>=20250,($C$5-1500)/1.25*0.25,($C$5/1.35)*0.25))
E5	E5	=IF(((C5-D5)/1.1*0.1)>1500,1500,((C5-D5)/1.1*0.1))
F5	F5	=C5-SUM(D5:E5)

Thank you Kevin that works great. Anyway may i ask what is the manual amount of 17999 & 20250 that you are checking in $C$5?

 

[kevin9999]

Thank you Kevin that works great. Anyway may i ask what is the manual amount of 17999 & 20250 that you are checking in $C$5?

They seem to be the amounts (beyond which) that the percentages returned go a bit haywire - given that you're looking at a maximum amount in one cell and a minimum in another. Those figures would change if your 25% & 10% changed. A mathematician would be able to explain it better than me