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. than...
excel
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