Announcement

Collapse
No announcement yet.

Multiple tasks within same column

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!


    Attached Files

  • #2
    What if column A is greater that 1 million or 3 million but column C doesn't match your criteria?
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    I am participating in the "Walk for a Cure" for the Kidney Foundation of Canada. If you can/want to, please Donate: https://kidney.akaraisin.com/ui/1674...cipant/1145052

    Comment


    • #3
      If Column C is Workers Compensation then it will always have a 1 million cap. If Column C is General Liability it will always have a 3 million cap. If Column C has no data, then leave column B blank.

      Comment


      • #4
        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

        I am participating in the "Walk for a Cure" for the Kidney Foundation of Canada. If you can/want to, please Donate: https://kidney.akaraisin.com/ui/1674...cipant/1145052

        Comment


        • #5
          Thank you, this is very close. Can you also add, show column A amount in Column B if it is below the cap?

          Comment


          • #6
            Try:


            =IF(TRIM(C2)="WORKERS COMPENSATION",MIN(A2,1000000),IF(TRIM(C2)="GENERAL LIABILITY",MIN(A2,3000000),""))
            Where there is a will there are many ways. Finding one that works for you is the challenge!

            MS Excel MVP 2010-2016

            I am participating in the "Walk for a Cure" for the Kidney Foundation of Canada. If you can/want to, please Donate: https://kidney.akaraisin.com/ui/1674...cipant/1145052

            Comment


            • #7
              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

              Comment


              • #8


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

                try:
                Code:
                ws.Range("K2").FormulaR1C1 = "=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

                I am participating in the "Walk for a Cure" for the Kidney Foundation of Canada. If you can/want to, please Donate: https://kidney.akaraisin.com/ui/1674...cipant/1145052

                Comment

                Working...
                X