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!
Announcement
Collapse
No announcement yet.
Multiple tasks within same column
Collapse
X

Multiple tasks within same column
Tags: None

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),""""))"

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
Leave a comment:

Try:
=IF(TRIM(C2)="WORKERS COMPENSATION",MIN(A2,1000000),IF(TRIM(C2)="GENERAL LIABILITY",MIN(A2,3000000),""))
Leave a comment:

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

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.
Leave a comment:

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.
Leave a comment:

What if column A is greater that 1 million or 3 million but column C doesn't match your criteria?
Leave a comment:
Leave a comment: