Forum Discussion

A_SIRAT's avatar
A_SIRAT
Iron Contributor
Feb 12, 2023

Excel formula to calculate balances and use unit of measurement

Hi,

I need to be assisted with formulas in the respective cells that will calculate the stock remaining and indicate the unit of measurement. I have explained what I want in the attached file.

thanks.

I am using office 2019.

excel

1 Reply

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 12, 2023

    A_SIRAT 

    If you do not have Excel 365 you will need to break the formula back down to columns or even cells.

    = LET(
    shortage,     IF(request>issueStore, request - issueStore, 0),
    required,     CEILING(shortage, qty),
    mainClosing,  mainStore * qty - required,
    issueClosing, issueStore  + required - request,
    totalStock,   mainClosing + issueClosing,
    summary, HSTACK(
      shortage,
      required / qty,
      mainClosing / qty,
      issueClosing,
      totalStock,
      totalStock + purchase
    ),
    unit, " " & HSTACK(issueUnit, supplyUnit, supplyUnit, issueUnit, issueUnit, issueUnit),
    IF(summary>0, TEXT(summary, "#,##0") & unit, "")
  )

    The formula is in the top-left cell of the output.

Resources