Convert text to numbers, then multiply and sum

[ironx]

Hey everyone, first time poster, so go easy on me. I need to take Range A, containing drop-down values, assign them a number according to their value, then multiply Range A by another range (Range B), then subtract the product from the Sum of Range B. NOTE: "Range A values" shouldn't actually appear on the spreadsheet, they are just included for reference.

<table style="width: 412px; height: 156px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 77pt;" width="103"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Range A</td> <td style="width: 77pt;" width="103">Range A Values</td> <td style="width: 48pt;" width="64">Range B</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">PTO</td> <td align="right">1</td> <td align="right">0.5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">HPTO</td> <td align="right">0.05</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">TRN</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">HTRN</td> <td align="right">0.05</td> <td align="right">1</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">HLDY</td> <td align="right">1</td> <td align="right">0.5</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt;" height="20">
</td> <td align="right">0</td> <td align="right">1
</td> </tr> </tbody></table>
Here's what I have for my VBA code so far, but I'm pretty sure I did some very illegal methods (like multiplying two ranges).

Logic goes as follows:
Input Range A and Range B
Sum Range B
Convert Range A to correct Values
Multiply Range A Values by Range B (row by row basis)
Sum the product
Subtract the product from the Sum of Range B

-----------------------------------------------------------------

Function FTE(TypeOff As Range, Multiplier As Range) As Double

Dim TypeItm As Range, MultItem As Range
Dim FTEMax As Double

FTEMax = WorksheetFunction.Sum(Multiplier)

For Each TypeItm In TypeOff
    If TypeItm = "PTO" Or TypeItm = "TRN" Or TypeItm = "HLDY" Then
        TypeItm = 1
    ElseIf TypeItm = "HPTO" Or TypeItm = "HTRN" Then
        TypeItm = 0.05
    Else
        TypeItm = 0
    End If
Next TypeItm

Dim FTEDaysOff As Range

FTEDaysOff = TypeOff * Multiplier

Dim GrossDaysOff As Long
GrossDaysOff = WorksheetFunction.Sum(FTEDaysOff)
    
FTE = FTEMax - GrossDaysOff

End Function

---------------------------------------------------

Unfortunately, I've looked into pretty much every workaround I can think of, and this is pretty much the only way I can do this (meaning I have to follow the logic listed above). It also has to be a self-contained function for ease of use, because someone besides myself will be updating this file later. I plan to write a how-to file, but want to keep it as clean as possible on the surface.

Thank you for your help!

Let me know if I can give more details that would help.

 

[Darren Bartrup]

So in your example you're trying to get:
1 x 0.5 = 0.5
0.05 x 1 = 0.05
1 x 1 = 1
0.05 x 1 = 0.05
1 x 0.05 = 0.5

Add the results to get 2.1?

If you don't mind having your "Range A Values" in column B and you "Range B" values in column C you could use

=SUMPRODUCT(B1:B5,C1:C5)

 

[ironx]

Actually, once I get to the numbers, I have no problem at all. It's converting the range from the "Range A" to "Range A Values" without them appearing anywhere in the spreadsheet.

I tried using custom formatting, and "covering up" Range A Values with Range A by using the quotations and such. This would be the easiest solution, but because the drop-down menu ability used through data validation doesn't also use the source formatting, when selected, the number appears instead of the description.

Hope this helps!

 

[Darren Bartrup]

How about:

Function FTE(TypeOff As Range, Multiplier As Range) As Double

    Dim TypeItm As Range
    Dim TypeOffValue As Double
    
    For Each TypeItm In TypeOff
        Select Case TypeItm
            Case "PTO", "TRN", "HLDY"
                TypeOffValue = 1
            Case "HPTO", "HTRN"
                TypeOffValue = 0.05
            Case Else
                TypeOffValue = 0
        End Select
        FTE = FTE + TypeOffValue * Multiplier(TypeItm.Row, 1)
    Next TypeItm

End Function

 

[ironx]

Did some (very) minor additions, and it worked! YOU ARE MY HERO! Thank you so much!

 

[Darren Bartrup]

No probs. Glad to help. And welcome to the board by the way.

 

[ironx]

Hello again, hopefully this thread doesn't die. New problem. The code that worked for my test cases (listed below) doesn't work when I put it into my huge, and complicated-formatted spreadsheet. After troubleshooting, I think it comes down to the range.row section of the code, and groupings I have set up on the spreadsheet. For whatever reason, the row indexes go haywire when used with groupings. My question now is: Can you somehow replace, or edit my code listed below to make it so the two ranges used as input to the function's relative location to eachother doesn't matter?

My only idea now is to try and used the MATCH function in some backward and complicated way to use the row numbers instead of row indexes. Hope this makes sense! I'll bold the code that is breaking below.

Function FTE(TypeOff As Range, Multiplier As Range) As Double

    Dim TypeItm As Range
    Dim TypeOffValue As Double
    Dim TimeOff As Double
    Dim Test As Double
    
    TimeOff = WorksheetFunction.Sum(Multiplier)
    
    For Each TypeItm In TypeOff
        Select Case TypeItm
            Case "PTO", "TRN", "HLDY"
                TypeOffValue = 1
                TimeOff = TimeOff - TypeOffValue * Multiplier([B]TypeItm.Row[/B])
            Case "HPTO", "HTRN"
                TypeOffValue = 0.5
                TimeOff = TimeOff - TypeOffValue * Multiplier([B]TypeItm.Row[/B])
            Case Else
                TypeOffValue = 0
                TimeOff = TimeOff - TypeOffValue * Multiplier([B]TypeItm.Row[/B])
        End Select
    Next TypeItm
    
    FTE = TimeOff
    
End Function

Other ideas which I haven't explored in doing in VB:
- Arrays/Vectors
- Somehow initializing the range's relative location to each other and using that

Thank you again for your help! I'll make sure I repay this kindness by trying to answer other questions people have posted.