Excel LAMBDA Lesson 2: GST, Lakhs and Money Functions

Excel LAMBDA Lesson 2: GST, Lakhs and Money Functions featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

πŸ“˜ Excel LAMBDA Library Course Β· Lesson 2 of 8

In this article
  1. The GST set
  2. Indian number display
  3. Common mistakes
  4. Practice
  5. Continue learning

These are the functions an Indian accounts team uses every day. Build them once and stop retyping rates.

Advertisement

The GST set

Name Refers to Example
GSTINCL =LAMBDA(amt,rate,ROUND(amt*(1+rate),2)) GSTINCL(1499,18%) = 1768.82
GSTONLY =LAMBDA(amt,rate,ROUND(amt*rate,2)) GSTONLY(5499,18%) = 989.82
GSTEXCL =LAMBDA(gross,rate,ROUND(gross/(1+rate),2)) GSTEXCL(1180,18%) = 1000
CGST / SGST =LAMBDA(amt,rate,ROUND(amt*rate/2,2)) Each half of intra-state GST
⚠️ Rounding rules differ: some businesses round GST per line, some on the invoice total. Decide once and put the rule inside the function so everyone follows it.

Indian number display

LAKHS   =LAMBDA(n, TEXT(n/100000, "0.00") & " L")      ' 25,00,000 β†’ 25.00 L
CRORE   =LAMBDA(n, TEXT(n/10000000, "0.00") & " Cr")   ' 12,34,56,789 β†’ 12.35 Cr
INRSHORT =LAMBDA(n, IF(n>=10000000, CRORE(n), IF(n>=100000, LAKHS(n), TEXT(n,"#,##0"))))

Note that INRSHORT calls the other two: functions in your library can use each other.

ROUNDTO5 =LAMBDA(n, MROUND(n, 5)) rounds cash amounts to the nearest β‚Ή5.

Advertisement

Common mistakes

  • Using text results in totals. LAKHS returns text for display; keep the real number in the data.
  • Rate typed as 18 instead of 18%. Add a guard: IF(rate>1,rate/100,rate).

Practice

Download the workbook below. The Data sheet has five items with their GST rates. The tasks call each function inline, like =LAMBDA(x,x*2)(A2), so the file works on any Microsoft 365 PC; in your own files, save them by name in Name Manager. The Check column turns green when you’re right.

πŸ“Ž Practice files for this article

Free to use for learning. Files with macros (.bas) are plain text β€” import them with Alt+F11 β†’ File β†’ Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong