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

## Files

• 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

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

• 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

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

• 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

• 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