Formula is not behaving as i wish it to do 2

[47redlands]

I am carrying out a Microsoft Excel exercise. It is for National Insurance.
The current national insurance rate ranges in the following manner; If you earn more than £110 a week and up to £844 a week, you pay 11 per cent of the amount you earn between £110 and £844. If you earn more than £844 a week, you also pay an extra 1 per cent of all your earnings over £844.

The formula for excel is calculated in two stages
IF(C6>110, IF(C6<844, "yes", "no"))
So if it returns no
So if the weekly pay is £576.9231 it will return with yes.

I am then using this formula:
=IF(I6="yes",C6*0.11,C6*0.11+(C6-844)*0.01)

What is wrong with this formula as it is not working as I want it to.

 

[SkipVought]

Hi,

This forum is for Visual Basic for Applications.

You have a spreadsheet formula that would be best addressed in forun68.

 

[zelgar]

Well, there are 3 cases you have here
[ol 1]
[li]Earn <= 110[/li]
[li]Earn between 110 and 844[/li]
[li]Earn > 844[/li]
[/ol]
You can combine all of them into the same formula and not worry about the Yes/No options with the following:
=IF(AND(C6>110,C6<844),IF(C6>844,C6*0.11+(C6-844)*0.01,C6*0.11),0)

Note: The above assumes that if you make less than 110, you pay 0. Change this if it's wrong.

 

[dhookom]

Would anyone else have a Variables worksheet with range for wages and rates rather than hard-coding values like this into expressions? I guess I'm a database guy who expects current national insurance rates to change and I would hate to edit expressions.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

You would have a table of rates.

Use a INDEX()/MATCH() function with +/- match code I can't remember which.

 

[SkipVought]

Here's a table WOTHOUT an As-Of Date...
[pre]
Amt Rate
0 0
111 0.11
845 0.12
[/pre]

formula where G2 is the earning amount that the rate is going to be calculated from...
[tt]
=INDEX(Rate,MATCH(G2,Amt,1))
[/tt]

I'm going to try next to include an As-Of Date so that historical rate data can be included.

 

[SkipVought]

Duane, the table should include an As-Of Date, Amt (Wage), Rate fields.

But there are some difficulties in defining a sub set of the table based on some date different than and specific As-Of Date in a formula using the OFFSET() function. I'm still playing with it. I think the table might need From-Thru As-Of Dates.

 

[47redlands]

=IF(AND(C6>110,C6<844),IF(C6>844,C6*0.11+(C6-844)*0.01,C6*0.11),0)

I tried Zelgars formula,

there is something wrong with the logic, for example on values about 844 it returns 0.

what is wrong with it?

 

[dhookom]

Depending on which rate is used for 844 try something like:

=IF(AND(C6>110,C6<[highlight #FCE94F]=[/highlight]844),IF(C6>844,C6*0.11+(C6-844)*0.01,C6*0.11),0)

Duane
Hook'D on Access
MS Access MVP

 

[combo]

You need one condition in the top IF:
=IF(C6>110,IF(C6>844,C6*0.11+(C6-844)*0.01,C6*0.11),0)
I would rather avoid embedding too many IFs, it can be hard to debug:
=IF(C6>110,0.11*C6+MAX(0,0.01*(C6-844)),0)

combo

 

[SkipVought]

Okay, so I'm using a Table with a UDF.

My Table
[pre]
Amt Rate
0 0
111 0.11
845 0.01
[/pre]

My UDF...

Function InsuranceRate(EARNINGS As Currency) As Currency
    Dim Rates, cAmt_2 As Currency, x
    Const NO_RATE = 1   'No rate
    Const RATE_1 = 2    'first percentage rate
    Const RATE_2 = 3    'final percentage rate
    Const AMT = 1       'The amount
    Const RTE = 2       'the rate
    
    Rates = [tInsRates[#Data]]     'get the rate table from the excel structured table
    
    cAmt_2 = EARNINGS - Rates(RATE_2, AMT)    'the earnings over the final earnings amount amount
    
    Select Case EARNINGS
        Case Is < Rates(RATE_1, AMT)
            InsuranceRate = 0
        Case Is < Rates(RATE_2, AMT)
'If you earn more than £110 a week and up to £844 a week, _
    you pay 11 per cent of the amount you earn between £110 and £844.
            InsuranceRate = InsuranceRate + _
                (EARNINGS - Rates(RATE_1, AMT)) * Rates(RATE_1, RTE)
        Case Else
'If you earn more than £844 a week, _
    you also pay an extra 1 per cent of all your earnings over £844.
            If cAmt_2 > 0 Then
                InsuranceRate = InsuranceRate + _
                    cAmt_2 * Rates(RATE_2, RTE)
            End If
            InsuranceRate = InsuranceRate + _
                (Rates(RATE_2, AMT) - Rates(RATE_1, AMT)) * Rates(RATE_1, RTE)
    End Select
End Function

 

[dhookom]

Thanks Skip. As usual, I learn a bunch from your posts. I assume the Rates = [tInsRates[#Data]] creates an array of table tInsRates?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Yes, I should have stated that my rate table is a Structured Table named tInsRates.