0

I have an Excel spreadsheet cell which looks as follows:

   A             | B           | C                    | D              | E
1  TASK          | WORK (days) | RESOURCES            | PLUMBER (days) | ELECTRICIAN (days)
2  Fit bathroom  | 3           | Plumber, Electrician | 1.5            | 1.5

Cells A2, B2 and C2 are entered manually.

The formula in cell D2 takes the total number of "Work" days (cell B2) and divides them by the number of resources specified in cell C2 (each resource is separated by a comma):

=IF(ISNUMBER(SEARCH("Plumber",$C2)),$B2/(LEN($C2)-LEN(SUBSTITUTE($C2,",",""))+1),0)

The formula for cell E2 is similar:

 =IF(ISNUMBER(SEARCH("Electrician",$C2)),$B2/(LEN($C2)-LEN(SUBSTITUTE($C2,",",""))+1),0)

I'd like to modify my forumla to be able to deal with the following kind of entry in the C2 cell:

Plumber, Electrician [200%]

In this case, cell D2 should be set to 1 and cell E2 should be set to 2.

I hope this makes sense and would appreciate any help on how to achieve this.

  • Considering possible complexity of inputs ("plumber [150%], electrician" and "plumber [50%], electrician, carpenter [120%]" ...) I don't suggest doing it with worksheet function, far more easy creating a function in VBA for that. – Máté Juhász Apr 1 '16 at 13:37
0

This macro function will work:

Public Function resourceDays(resourceName As String, totalDays As Integer, totalResources As String)
    resourceDays = 0
    Dim eachResource() As String
    eachResource = Split(totalResources, ",")
    totalValues = 0
    For i = 0 To UBound(eachResource)
        thisresource = eachResource(i)
        startPct = InStr(thisresource, "[")
        If startPct = 0 Then
            resourceValue = 1
            totalValues = totalValues + resourceValue
            If UCase(Trim(thisresource)) = UCase(Trim(resourceName)) Then
                thisvalue = resourceValue
            End If
        Else
            endPct = InStr(thisresource, "%")
            PctValue = (Mid(thisresource, startPct + 1, endPct - startPct - 1)) / 100
            Tempresource = Mid(thisresource, 1, startPct - 1)
            resourceValue = PctValue
            totalValues = totalValues + resourceValue
            If UCase(Trim(Tempresource)) = UCase(Trim(resourceName)) Then
                thisvalue = resourceValue
            End If



        End If
    Next i
    resourceDays = totalDays * (thisvalue / totalValues)
 End Function

Open VBA / Macros with Alt+ F11, under ThisWorkbook insert a new module and paste the code on the right side.

On cell D2 put the formula =resourceDays(D1,B2,C2), where the parameters are

resourceDays=(Name of Resource, Total Of Days, String Of Total Resources).

0

As Mate Juhasz pointed out, it is very hard to do it with worksheet function, but not impossible.

If you really want to do it then you had better use temporary variable (Use some cells to store temporary value) then hide them. (for example, set width of column G = 0)

You can do that like this:

G1 =SEARCH(",", C2)
G2 =TRIM(MID(C2,G1+1,LEN(C2)))
G3 =TRIM(MID(C2,G1+1,LEN(C2)))
G4 =MID(G2, SEARCH("[",G2)+1, SEARCH("]", G2)-SEARCH("[",G2)-1)
G5 =MID(G3, SEARCH("[",G3)+1, SEARCH("]", G3)-SEARCH("[",G3)-1)
G6 =IF(ISNUMBER(G4+0),G4+0,1)
G7 =IF(ISNUMBER(G5+0),G5+0,1)
G8 =B2 / (G6+G7) * G6
G9 =B2 / (G6+G7) * G7

D3 =IF(ISNUMBER(SEARCH("Plumber",G3)), G9, G8)
E3 =B2-D2

If you don't want to use these temporary variable then you can replace every cell with its value, but formula would be very long.

However, just as Mate Juhasz pointed, that is very hard. Use macro is better.

0

Achieving your goal will be really complicated as other mates mentioned. A better way to achieve your goal is to think numerically. I see if you change the way of cells working will be more effective. For instance, make column D & E manually modified. While column C has IF statement that will search in D and E columns for numbers input. This will make it easier for you to modify or add any other functions within the sheet. This is an example of what I meant :

=IF(AND(ISNUMBER(D3),ISNUMBER(E3)),"Plumber, Electrician",IF(ISNUMBER(D3),"Plumber",IF(ISNUMBER(E3),"Electrician","")))

When you use the above function in column C, it will search for number input in column D and E, and put the correct resources. From the same function, you can also extend it further.

The reason that I changed the way of input, is because Excel meant to be working with numbers, and also typing numbers is faster that typing strings, as you will save time. Think about it again.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.