I am using Import-Excel to calculate some data using excel formula.

Below is the code

# Import the ImportExcel module
Import-Module ImportExcel

# Define the path to the existing Excel file
$excelFilePath = "E:VLANReports20240702_VLAN_Data.xlsx"

# Import ImportExcel Module
Import-Module ImportExcel

# Define the path to the new Excel file
$newExcelFilePath = "E:VLAN.xlsx"

# Read the data from the "USER" sheet
Write-Output "Reading USER data from the new file..."
$userData = Import-Excel -Path $newExcelFilePath -WorksheetName "USER"

# Check if user data was imported
if ($userData -eq $null) {
    Write-Output "No data found in USER sheet. Exiting script."
    exit
}

# Create a new worksheet "Sheet1" and copy data from "USER"
Write-Output "Exporting USER data to Sheet1..."
$userData | Export-Excel -Path $newExcelFilePath -WorksheetName "Sheet1" -ClearSheet -AutoSize -TableName "UserData"

# Open the Excel package
Write-Output "Opening Excel package..."
$excelPackage = Open-ExcelPackage -Path $newExcelFilePath

# Get the worksheet
$sheet1 = $excelPackage.Workbook.Worksheets["Sheet1"]

if ($sheet1 -eq $null) {
    Write-Output "Sheet1 not found. Exiting script."
    exit
}

# Define the headers to be added starting from column N
$headers = @(
    "Bckp`nSG VLanID",
    "Bckp`nnetwork label",
    "Bckp`nSubnet",
    "Bckp`nNetmask",
    "Bckp`nGateway",
    "Bckp`nNetwork"
)

# Add headers manually to "Sheet1"
Write-Output "Adding headers to Sheet1..."
for ($i = 0; $i -lt $headers.Length; $i++) {
    $columnIndex = 14 + $i
    $headerCell = $sheet1.Cells[1, $columnIndex]
    $headerCell.Value = $headers[$i]
    $headerCell.Style.WrapText = $true
}

# Define the formulas for columns N, O, P, Q, R, S
$formulas = [ordered]@{
    N = '=IFERROR(IFS(MID(C2,1,1)="2",XLOOKUP(NUMBERVALUE(MID(C2,2,3)),BACKUP!$C:$C,BACKUP!$C:$C),MID(C2,1,1)="3",XLOOKUP(C2+300,BACKUP!$C:$C,BACKUP!$C:$C)),"")'
    O = '=IF(N2<>"",XLOOKUP(N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
    P = '=IF(N2<>"",LEFT(S2,SEARCH("/",S2,1)-1),"")'
    Q = '=IF(S2<>"", LEFT(S2, FIND("/", S2)-1) & " (" & TEXTJOIN(".", TRUE, MID(S2, FIND("/", S2), LEN(S2))) & ")", "")'
    R = '=IF(N2<>"",XLOOKUP(N2,BACKUP!$C:$C,BACKUP!$G:$G),"")'
    S = '=IF(N2<>"",XLOOKUP(N2,BACKUP!$C:$C,BACKUP!$A:$A),"")'
}

# Get the last row in the sheet
$lastRow = $sheet1.Dimension.End.Row
Write-Output "Last row in Sheet1: $lastRow"

# Add formulas to "Sheet1"
Write-Output "Adding formulas to Sheet1..."
$formulas.Keys | ForEach-Object {
    $column = $_
    $colIndex = [array]::IndexOf($formulas.Keys, $column) + 14
    for ($row = 2; $row -le $lastRow; $row++) {
        $cell = $sheet1.Cells[$row, $colIndex]
        $cell.Formula = $formulas[$column] -replace '2', $row
    }
}

# Force recalculation of all formulas in the workbook
Write-Output "Forcing recalculation of all formulas..."
$sheet1.Calculate()

# Save and close the Excel package
Write-Output "Saving the Excel package..."
Close-ExcelPackage $excelPackage

Write-Output "Sheet 'Sheet1' has been created with the 'USER' data, headers added, and formulas applied."

The problem is in the cells (column N onwards), I am able to see only the formula being displayed not the calculated data.

Please let me know what is wrong with the code.