📎 This article includes 1 downloadable practice file ↓
In this article
These are the functions an Indian accounts team uses every day. Build them once and stop retyping rates.
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 |
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.
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
- 📗Lesson 2 practice workbook7 money and GST functions to build and check.⬇ XLSX · 14 KB
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.
Stuck on a step? Ask a question and the AI answers using this article.