Excel Help , formula

[bilbonvidia]

I am trying to work out a way to get payroll net to gross figures given only net and a taxcode. I have created a spreadsheet that will give gross to net and deductions. Please see the linked spreadsheet. I have put a some results in a table underneath. Is it possible to get these figures to change automatically when the tax code above changes?

If so is it then possible to some how be able to type into a cell a net figure and have the gross figure returned using the table? Or can this be done via a formula using the figures in the table? Any help is greatly appreciated.

http://www.mediafire.com/?drftjpnyetu"]Spreadsheet Clicky[/URL

 

[bilbonvidia]

I have automated goal seek using

'Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/07/2007 by WAAM
'
Dim V$
V$ = InputBox("Enter Value")

If V$ = "" Then Exit Sub

Range("F16").Select
Range("F16").GoalSeek Goal:=V$, ChangingCell:=Range("B10")

End Sub"

Next question:

Now I am trying to get the below value to be rounded to the neares 0.5:

=iF(F8="HR",B10,IF(B10>((F8*10)+9)/52,B10-(((F8*10)+9)/52),0)),

I am trying to use MROUND but cant get it to work

=MROUND(IF(F8="HR",B10,IF(B10>((F8*10)+9)/52,B10-(((F8*10)+9)/52),0)),0.5)

can it be done without putting the mround function into a separate cell?

 

[bilbonvidia]

That does work, VBA Analysis tool pack is needed for MLOOKUP, which I had installed in another excel window but had not reopened the excel workbook I was trying the formula in.

 

[LaurieOZ]

Hi bilbonvidia
I have created a formula using sumproduct function
will make the goal seek easier

Gross Pay 409.44
band 10500 201.9230769
Taxcode 885 170.1923077
Sum of 201.92+170.19 372.1153846

Tax & NI 57.85
Net 351.59

Tax Table Inc Level Tax Rate RateDifference
Tax Free Limit 170.1923077 0.10 0.10
1st tax rate change 201.9230769 0.18 0.08

Formula for tax & NI is

=ROUND(SUMPRODUCT(--(B1>{170.19;372.11}),(B1-{170.19;372.11}),{0.1;0.08}),2)+ROUND(MIN(57,(B1-100)*0.1),2)

Hope this helps
Laurie