excel vb help

[Dr Delphi]

I'm trying to create a VB function which compares two different KS3 scores and returns a residuals between the two, im at a bit of a loss as the residual needs to be in a form of base three for the decimal but whole numbers for the rest, i.e.:

3c
3b
3a
4c
4b
4a
etc. is the scoring, and the residuals look like:

6c -> 7c = -1.0
5a -> 3b = 2.1

Would anyone be able to help?

 

[Flanders]

A crude way of doing it would be to replace the "a,b,c" with their numeric equivalents. Assuming actual scores are in ColA, stick this in ColB:

=SUBSTITUTE(A1,RIGHT(A1,1),IF(RIGHT(A1,1)="a",".1",IF(RIGHT(A1,1)="b",".2",".3")))

You should be able to use ColB to find the differences.

 

[dazzerd]

If I could understand what you mean I could write you a function, but my maths knowledge is poor. Could you give a better example?

Edit: Or are you saying 4c = 4.3 and 5d = 5.4, therefore 5d - 4c would equal 1.1? If so that's straightforward, I'll put you a UDF together when my son goes down this evening.

 

[Dr Delphi]

I'd already come up with some of those ideas, ive spent quite a few hours trying to work out the intricacies as sometimes it doesn't work because the decimal is in base 3 but not the whole number it causes problems.

The way I've had it explained by the data person in school is that the following is part of the lookup table, which then they use the residual as a whole number of sublevels (each whole level is divided into a,b,c so it goes 4a, 5c, 5b, 5a, 6c).

2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
5a 35
6c 37
6b 39
6a 41
7c 43
7b 45
7a 47

Tgt CWL Residual
5c 6b 31 39 8 4
4a 3b 29 21 -8 -4

This is all well and good when its a few to be calculated but as I am in charge of all the most able kids at school I need to be able to go through all 1200 students quickly, so I am trying to make a new function called Resid(A1, A2) where A1 is the first level (target) and A2 the second CWL level, the difference between the two would be 4 (using 5c and 6b) which needs to be reinterpreted as 1.1 whole levels.

I don't know if that helps with my query at all?

 

[dazzerd]

No

The issue here then seems to be the logic rather than problems figuring out the vba code which would be something like:

Public Function resid(byVal arg1 as string, byval arg2 as string) as integer

Dim result as integer

'some code here

resid = result

End Function

I'm assuming here that what you want to return is an integer. But what we needs to know for clarity is what arg1 and arg2 is going to be and what you want to do with the parameters.

If say arg1 = 4c we could add something like.

Dim baseStuff as byte
Dim tempArg1 as variant

Select Case right(arg1,1)
Case "a"
baseStuff = 1

Case "b"
baseStuff = 2

Case "c"
baseStuff = 3

Case else' this here would catch if it was just a whole number (or any other number but we can make this smarter if we need)

basestuff = 0
' no need for this really here as basestuff will be 0 anyway, but you get the point
End Select

temparg1 = left(arg1,1) & "." & basestuff

temparg1 = cdec(temparg1)

This will convert 4c to a decimal of 4.3. I;ve not tested that its from the top of my head by the way but it should work. You could do the same for arg2 and do whatever maths needs ding on 2 decimals then.

 

[andshrew]

If I've understood right, if you put the following into a module it should give you a new function called resid which you can use as follows:

=resid("target","cwl")

eg. =resid("3b","5a")

Option Explicit

Function resid(ByVal target As String, ByVal cwl As String) As Single

    Dim target_calc As Single, cwl_calc As Single

    target_calc = convert_grade(target)
    cwl_calc = convert_grade(cwl)
    
    resid = cwl_calc - target_calc

End Function

Private Function convert_grade(ByVal grade As String) As Single
    Dim grade_split(0 To 1) As Single
    
    grade_split(0) = Mid(grade, 1, 1)
    
    Select Case UCase(Mid(grade, 2, 1))
        Case "C"
            grade_split(1) = 0.1
    
        Case "B"
            grade_split(1) = 0.2
    
        Case "A"
            grade_split(1) = 0.3
            
        Case Else
            grade_split(1) = 9000
    End Select
    
    convert_grade = grade_split(0) + grade_split(1)

End Function

You may want to add some error checking; I've only included a very basic 'if not A, B or C return a ridiculous value' check. Hope that helps. It seems to work with your examples.

 

[Dr Delphi]

Thanks andshrew and dazzerd, the only time ive found so far that it doesn't return the value I expect is when the cwl is lower than the target, for example:
4a 5c gives 0.8 which should be 0.1 as its only one sub level increase, this is where my rusty logic from phd days started to fail me as I could never make it work for all the possible cases!

 

[andshrew]

I must have managed to try only ones that worked before I posted that...!

If you still need it... this basically calculates it by looking at whether they are under or over target, and whether or not they are within '1' of their target. I think this should now work in all instances... there's probably a more elegant way of working this out but I'll leave that for you to play around with.

Option Explicit

Function resid(ByVal target As String, ByVal cwl As String) As Single

    Dim target_calc As Single, cwl_calc As Single

    target_calc = convert_grade(target)
    cwl_calc = convert_grade(cwl)
    
    Dim tmp_cwl As Single: tmp_cwl = Round(cwl_calc - Int(cwl_calc), 1)
    Dim tmp_target As Single: tmp_target = Round(target_calc - Int(target_calc), 1)

    Select Case Int(cwl_calc)
        Case Is > Int(target_calc) ' Over Target
            If (Int(cwl_calc) - Int(target_calc) > 1) Then
                If tmp_cwl < tmp_target Then
                    resid = Int(cwl_calc) - Int(target_calc)
                    resid = resid + IIf(tmp_target = 0.3, tmp_cwl, tmp_target) - 1
                Else
                    resid = cwl_calc - target_calc
                End If
            Else
                If tmp_cwl < tmp_target Then
                    resid = IIf(tmp_target = 0.3, tmp_cwl, tmp_target)
                Else
                    resid = cwl_calc - target_calc
                End If
            End If
            
        Case Is < Int(target_calc) ' Under Target
            If (Int(cwl_calc) - Int(target_calc) < -1) Then
                If tmp_cwl > tmp_target Then
                    resid = Int(cwl_calc) - Int(target_calc)
                    resid = resid - IIf(tmp_cwl = 0.2, tmp_cwl, tmp_target) + 1
                Else
                    resid = cwl_calc - target_calc
                End If
            Else
                If tmp_cwl > tmp_target Then
                    resid = IIf(tmp_cwl = 0.2, tmp_cwl, tmp_target)
                    resid = -resid
                Else
                    resid = cwl_calc - target_calc
                End If
            End If
            
        Case Else
            resid = cwl_calc - target_calc
    End Select

End Function

Private Function convert_grade(ByVal grade As String) As Single
    Dim grade_split(0 To 1) As Single
    
    grade_split(0) = Mid(grade, 1, 1)
    
    Select Case UCase(Mid(grade, 2, 1))
        Case "C"
            grade_split(1) = 0.1
    
        Case "B"
            grade_split(1) = 0.2
    
        Case "A"
            grade_split(1) = 0.3
            
        Case Else
            grade_split(1) = 9000
    End Select
    
    convert_grade = grade_split(0) + grade_split(1)

End Function