Sep 5 2025, 07:48 AM
QUOTE(soonwai @ Sep 5 2025, 02:23 AM)
Thought I share this here. Hope someone finds it useful.
This is a TNB single cell formula for new tariff non-ToU. (Later add ToU)
• it'll use the correct AFA for both months (hopefully)
• calculation may be off if used for less a full billing period (28, 30, 31 days)
• sometimes still off by 1 sen (rounding error somewhere)
Example:
You enter Billing Start Date and Usage. Formula goes into the 3rd cell.
You will need to setup Tables & Named cells. The ones in purple.
Tables (3):
TenagaNonTOU_Table
ICT_Table
AFA_Table
Named Cells (5):
kapasiti
rangkaian
peruncitan
sst
kwtbb
Remember to change:
_startdate,A2,
_kwh,B2,
to your respective cells.
Later upload Excel file.
I think Tnb calculator is already comprehensive. It’s available at their website.This is a TNB single cell formula for new tariff non-ToU. (Later add ToU)
• it'll use the correct AFA for both months (hopefully)
• calculation may be off if used for less a full billing period (28, 30, 31 days)
• sometimes still off by 1 sen (rounding error somewhere)
Example:
You enter Billing Start Date and Usage. Formula goes into the 3rd cell.
You will need to setup Tables & Named cells. The ones in purple.
Tables (3):
TenagaNonTOU_Table
ICT_Table
AFA_Table
Named Cells (5):
kapasiti
rangkaian
peruncitan
sst
kwtbb
Remember to change:
_startdate,A2,
_kwh,B2,
to your respective cells.
CODE
=LET(
_startdate,A2,
_kwh,B2,
_kapasiti,kapasiti,
_rangkaian,rangkaian,
_peruncitan,peruncitan,
_sst,sst,
_kwtbb,kwtbb,
_kwhNoST,IF(_kwh>600,600,_kwh),
_kwhST,IF(_kwh>600,_kwh-600,0),
_rate,XLOOKUP(_kwh,TenagaNonTOU_Table[Tenaga],TenagaNonTOU_Table[Rate],"not found",1),
_ICTRate,XLOOKUP(_kwh,ICT_Table[ICT],ICT_Table[Rate],"not found",1),
_ICTNoST,IF(_kwh>600,_ICTRate*600,_ICTRate*_kwh),
_ICTST,IF(_kwh>600,_ICTRate*(_kwh-600),0),
_subTotalKBB,_rate*_kwhNoST + _rate*_kwhST+_kapasiti*_kwhNoST+_kapasiti*_kwhST+_rangkaian*_kwhNoST+_rangkaian*_kwhST+_ICTNoST+_ICTST,
_prorateA,(DAYS(EOMONTH(_startdate,0),_startdate)+1)/(DAY(EOMONTH(_startdate, 0))),
_AFARateA,XLOOKUP(EOMONTH(_startdate,-1)+1,AFA_Table[AFA],AFA_Table[Rate],"not found",-1),
_prorateB,(DAYS(EDATE(_startdate,1),EOMONTH(_startdate,0)+1))/(DAY(EOMONTH(_startdate, 0))),
_AFARateB,XLOOKUP(EOMONTH(_startdate,0)+1,AFA_Table[AFA],AFA_Table[Rate],"not found",-1),
_subTotalkwhST,_rate*_kwhST+(_prorateA*_kwhST*_AFARateA)+(_prorateB*_kwhST*_AFARateB)+_kapasiti*_kwhST+_rangkaian*_kwhST+IF(_kwh>600,_peruncitan,0)+_ICTST,
ROUND(_rate*_kwhNoST + _rate*_kwhST,2)
+ ROUND(IF(_kwh>600,(_prorateA*_kwhNoST*_AFARateA)+(_prorateA*_kwhST*_AFARateA),0),2)
+ ROUND(IF(_kwh>600,(_prorateB*_kwhNoST*_AFARateB)+(_prorateB*_kwhST*_AFARateB),0),2)
+ ROUND(_kapasiti*_kwhNoST+_kapasiti*_kwhST,2)
+ ROUND(_rangkaian*_kwhNoST+_rangkaian*_kwhST,2)
+ ROUND(IF(_kwh>600,_peruncitan,0),2)
+ ROUND(_ICTNoST+_ICTST,2)
+ ROUND(IF(_kwh>600,_sst*_subTotalkwhST,0),2)
+ ROUND(IF(_kwh>300,_kwtbb*(_subTotalKBB),0),2)
)
_startdate,A2,
_kwh,B2,
_kapasiti,kapasiti,
_rangkaian,rangkaian,
_peruncitan,peruncitan,
_sst,sst,
_kwtbb,kwtbb,
_kwhNoST,IF(_kwh>600,600,_kwh),
_kwhST,IF(_kwh>600,_kwh-600,0),
_rate,XLOOKUP(_kwh,TenagaNonTOU_Table[Tenaga],TenagaNonTOU_Table[Rate],"not found",1),
_ICTRate,XLOOKUP(_kwh,ICT_Table[ICT],ICT_Table[Rate],"not found",1),
_ICTNoST,IF(_kwh>600,_ICTRate*600,_ICTRate*_kwh),
_ICTST,IF(_kwh>600,_ICTRate*(_kwh-600),0),
_subTotalKBB,_rate*_kwhNoST + _rate*_kwhST+_kapasiti*_kwhNoST+_kapasiti*_kwhST+_rangkaian*_kwhNoST+_rangkaian*_kwhST+_ICTNoST+_ICTST,
_prorateA,(DAYS(EOMONTH(_startdate,0),_startdate)+1)/(DAY(EOMONTH(_startdate, 0))),
_AFARateA,XLOOKUP(EOMONTH(_startdate,-1)+1,AFA_Table[AFA],AFA_Table[Rate],"not found",-1),
_prorateB,(DAYS(EDATE(_startdate,1),EOMONTH(_startdate,0)+1))/(DAY(EOMONTH(_startdate, 0))),
_AFARateB,XLOOKUP(EOMONTH(_startdate,0)+1,AFA_Table[AFA],AFA_Table[Rate],"not found",-1),
_subTotalkwhST,_rate*_kwhST+(_prorateA*_kwhST*_AFARateA)+(_prorateB*_kwhST*_AFARateB)+_kapasiti*_kwhST+_rangkaian*_kwhST+IF(_kwh>600,_peruncitan,0)+_ICTST,
ROUND(_rate*_kwhNoST + _rate*_kwhST,2)
+ ROUND(IF(_kwh>600,(_prorateA*_kwhNoST*_AFARateA)+(_prorateA*_kwhST*_AFARateA),0),2)
+ ROUND(IF(_kwh>600,(_prorateB*_kwhNoST*_AFARateB)+(_prorateB*_kwhST*_AFARateB),0),2)
+ ROUND(_kapasiti*_kwhNoST+_kapasiti*_kwhST,2)
+ ROUND(_rangkaian*_kwhNoST+_rangkaian*_kwhST,2)
+ ROUND(IF(_kwh>600,_peruncitan,0),2)
+ ROUND(_ICTNoST+_ICTST,2)
+ ROUND(IF(_kwh>600,_sst*_subTotalkwhST,0),2)
+ ROUND(IF(_kwh>300,_kwtbb*(_subTotalKBB),0),2)
)
Later upload Excel file.
It includes the start and end date like yours too.
This post has been edited by bee88: Sep 5 2025, 07:49 AM
Quote
0.0119sec
0.44
7 queries
GZIP Disabled