Multiple tasks within same column

  • I need a formula that will do the following;


    If Column A is greater than or equal to 1 million AND Column C says Workers Compensation insert 1 million in Column B. If Column A is less than 1 million, insert amount of column A.


    If Column A is greater than or equal to 3 million AND Column C says General Liability, insert 3 million in Column B. If Column A is less than 3 million, insert amount of column A.


    I am open to what ever you think is the best solution. Sample spreadsheet is attached.
    Thank you!

  • Doesn't seem to be the case in your sample data....


    anyway, give this a go:


    =IF(TRIM(C2)="WORKERS COMPENSATION",IF(A2>=1000000,1000000,""),IF(TRIM(C2)="GENERAL LIABILITY",IF(A2>=3000000,3000000,""),""))


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • This formula worked perfectly. Are you able to inset it into the following macro in the bolded line and remove the old formula? I don't know how to convert it to VBA.


    Sub InsertCol()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim LastRow As Long
    For Each ws In Sheets
    If ws.Name <> "Summary" Then
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws.Columns("K:K").Insert
    ws.Range("K1").FormulaR1C1 = "Incurred Total Within Deductible"
    ws.Range("K2").FormulaR1C1 = "=IF(RC[-1]<1000000,(RC[-1]),""1000000"")"
    ws.Range("K2").AutoFill Destination:=ws.Range("K2:K" & LastRow), Type:=xlFillDefault
    ws.Range("K" & LastRow + 1) = WorksheetFunction.Sum(ws.Range("K2:K" & LastRow))
    ws.Range("K" & LastRow + 2) = ws.Range("K" & LastRow + 1).Value * 0.1
    ws.Range("K" & LastRow + 3) = WorksheetFunction.Sum(ws.Range("K" & LastRow + 1) + ws.Range("K" & LastRow + 2))
    ws.Range("K" & LastRow + 1 & ":K" & LastRow + 3).NumberFormat = "#,##0.00"
    ws.Range("L" & LastRow + 1) = "Sub Total "
    ws.Range("L" & LastRow + 2) = "LCF @ 10% of Subtotal above"
    ws.Range("L" & LastRow + 3) = "Total Incurred Within Deductible"
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "A").End(xlUp).Offset(1, 0) = ws.Name
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "B").End(xlUp).Offset(1, 0) = ws.Range("K" & LastRow + 1)
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "C").End(xlUp).Offset(1, 0) = ws.Range("K" & LastRow + 2)
    Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "D").End(xlUp).Offset(1, 0) = ws.Range("K" & LastRow + 3)
    End If
    Next ws
    Application.ScreenUpdating = True
    End Sub

  • Sorry for the delay.... was on vacation...


    try:

    Code
    1. [B]ws.Range("K2").FormulaR1C1 = "[/B]=IF(TRIM(RC[-8])=""WORKERS COMPENSATION"",MIN(RC[-10],1000000),IF(TRIM(RC[-8])=""GENERAL LIABILITY"",MIN(RC[-10],3000000),""""))"

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016