Excel Formulas and Calculations
1. Salary Sheet
House Rent Calculation:
55% of basic salary for Designation: Principal and IT Officer.
50% of basic salary for Designation: Lead Trainer and JPO.
60% of basic salary for other designations.
Formula:
=IF(OR(Designation="Principal", Designation="IT Officer"), Basic*55%, IF(OR(Designation="Lead Trainer",Designation="JPO"), Basic*50%,Basic*60%))
2. Weekly Wages
Basic Wages:
Up to 40 hours is basic wages. Hours above 40 are count as overtime hours:
Grade 1: 150 Tk/hour.
Grade 2: 125 Tk/hour.
Grade 3: 100 Tk/hour.
Formula:
=IF(Designation="Grade 1", MIN(Hours,40)*150, IF(Designation="Grade 2", MIN(Hours,40)*125, IF(Designation="Grade 3", MIN(Hours,40)*100, 0)))Overtime Wages:
Hours above 40 are calculated as overtime.
Overtime rate: 200 Tk/hour.
Formula:
=IF(Hours>40,(Hours-40)*200, 0)
3. Electricity Bill
Electricity Charge:
0-100 Units: 10 Tk/unit.
101-200 Units: 12 Tk/unit.
Above 200 Units: 15 Tk/unit.
Minimum Charge: 200 Tk.
Formula:
=MAX(200,IF(CU<=100,CU*10,IF(CU<=200,100*10+(CU-100)*12,IF(CU>200,100*10+100*12+(CU-200)*15))))
- Service Charge
Area-specific charges:
Area 1: 300 Tk
Area 2: 500 Tk
Area 3: 600 Tk
Area 4: 700 Tk
Formula:
=IF(Area="Area-1",300, IF(Area="Area-2",500, IF(Area="Area-3",600,IF(Area="Area-4",700))
4. Average Bill for Area-2
Formula:
=AVERAGEIF(Area:Area,"Area-2", BillAmount:BillAmount)
5. Computer Shop
Discount {Grand Total (GT)}
9% discount if GT >500000.
3% discount if GT > 300000.
1% discount if GT < 300000.
Formula:
=IF(GT>500000,GT*9%, IF(GT>300000,GT*3%, IF(GT<300000,GT*1%,0)))
6. Result Sheet
Grading System:
100-80: A+
79-70: A
69-60: A-
59-50: B
49-40: C
39-33: D
Below 33: F
Formula:
=IF(Marks>=80,"A+", IF(Marks>=70,"A",IF(Marks>=60, "A-", IF(Marks>=50,"B", IF(Marks>=40,"C", IF(Marks>=33, "D",IF(Marks<33,"F"))))))গ্রেড পয়েন্ট অনেকটা এর মতোই
7. Cricket Match
Unique Jersey Number:
4-digit Alpha-numeric value.
Formula:
=AND(COUNTIF(Jersey:Jersey, JerseyNo)=1,LEN(JerseyNo)=4)এই সূত্রটি ডাটা ভেলিডেশন থেকে ফর্মুলা বারে লিখতে হবে।
Total Runs:
20 runs per wicket + sum of all runs.
Formula:
=SUM(Run+Run+Run)+SUM(Wkts+Wkts+Wkts)*20
Player of the Match:
Highest runs যার তার নাম শো করবে বাঁকা স্টাইলে.
Formula:
=INDEX(Name:Name, MATCH(MAX(Total:Total), Total:Total))
Rating:
1 star per 100 run.
Formula:
=IF(TotalRun<100," ",REPT("*", INT(TotalRun/100))অথবা এই সূত্র
=TotalRun/100
8. Age Categories
Classification:
1-12 years: Children.
18-25 years: Youth.
26-44 years: Adults.
Formula:
=IF(AND(Age>=1,Age<=12), "Children",IF(AND(Age>=18, Age<=25),"Youth", IF(AND(Age>=26,Age<=44), "Adult")))
9. Wage Calculation
Regular and Overtime:
Regular: 250 Tk/hour.
Overtime: 500 Tk/hour.
8 Hours is basic, above count as over time.
Formula: Amount
=IF(WorkingHours>8,WorkingHours-Overtime)*250+Overtime*500)Formula: Overtime-
=IF(WorkingHour>8,WorkingHour-8,0)
10.Result Sheet
Total: If obtained marks of all subjects are pass than pass otherwise print "F" and pass mark is 40
Formula:
=IF(OR(Bangla<40,English<40,Math<40),"F",SUM(Bangla:Math))
যদি অনুপস্থিত এ ফেল করাতে চাই তাহলে নিচের ফর্মুলা
=IF(OR(Bangla="A",English="A",Math="A"),"F",IF(OR(Bangla<40,English<40,Math<40),"F",SUM(Bangla:Math)
Letter Grade:
Total মার্ক ৮০% এর বেশি/সমান হলে A,৬০-৭৯% হয় তাহলে B,৪০-৫৯% হয় তাহলে C শো করাতে হবে,টোটাল ফেল থাকলে ফেল।
Formula: =IF(Total=F,"F",IF(Total>=300*80%,"A",IF(Total>=300*60%,"B",IF(Total>=300*40%,"C","F"))))
Rank: Total এর উপর ভিত্তি করে,টোটাল F থাকলে F শো হবে।
Formula:
=IF(Total=F,"F",RANK(Total,Total:Total,0))
No Of Pass:
Formula:
=COUNT(Total:Total)
