# Excel formula

#### baidya91

##### Board Regular
A kind of leave called commuted leave is credited half the half pay leave (another leave) every year. While commuted leave is credited maximum 30 in a service period, half pay leave is credited 60 in a service period (15 every year). Now if A1 = Half Pay Leave Credit, B1=Commuted Leave Credit and C1=Commuted Leave Availed, which excel formula has to be used to calculate the Commuted Leave Credit in B1? Please help...

https://www.dropbox.com/s/2pmrofd8w7...EAVE.xlsx?dl=0

Last edited by a moderator:

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
A kind of leave called commuted leave is credited half the half pay leave (another leave) every year. While commuted leave is credited maximum 30 in a service period, half pay leave is credited 60 in a service period (15 every year). Now if A1 = Half Pay Leave Credit, B1=Commuted Leave Credit and C1=Commuted Leave Availed, which excel formula has to be used to calculate the Commuted Leave Credit in B1? Please help...

https://www.dropbox.com/s/2pmrofd8w7...EAVE.xlsx?dl=0
..

..
 Name of the school Name of the staff: DAY MONTH YEAR DOB 22 10 1975 DOJ 25 1 2001 DOR 31 10 2035 Length of service Half Pay Leave Commuted Leave 5 30 From To Credited Enjoyed Balance Eligible Enjoyed 25/01/2002 24/01/2003 15 5 2 1 4 25/01/2003 24/01/2004 15 0 3 1 7 25/01/2004 24/01/2005 15 8 4 5 25/01/2005 24/01/2006 15 3 1 10 25/01/2006 24/01/2007 15 10 5 4 IF I17 = 5, THEN H18 WILL BE -1, IF I17 = 4, THEN H18 WILL BE 0 AND IN BOTH CASES ELIGIBLE COMMUTED WILL NOT BE CREDITED AS MAXIMUM NO OF COMMUTED LEAVE IS 30, THEREFORE CELLS FROM H19 TO H62 WILL BE NIL OR BLANK. PLEASE SUGGEST A SUITABLE EXCEL FORMULA TO DO THAT. 25/01/2007 24/01/2008 15 25 12 25/01/2008 24/01/2009 15 40 20 25/01/2009 24/01/2010 15 55 27 25/01/2010 24/01/2011 15 60 30 25/01/2011 24/01/2012 15 60 30 25/01/2012 24/01/2013 15 60 30 25/01/2013 24/01/2014 15 60 30 25/01/2014 24/01/2015 15 60 30 25/01/2015 24/01/2016 15 60 30 25/01/2016 24/01/2017 15 60 30 25/01/2017 24/01/2018 15 60 30 25/01/2018 24/01/2019 15 60 30 25/01/2019 24/01/2020 15 60 30 25/01/2020 24/01/2021 15 60 30 25/01/2021 24/01/2022 15 60 30 25/01/2022 24/01/2023 15 60 30 25/01/2023 24/01/2024 15 60 30 25/01/2024 24/01/2025 15 60 30 25/01/2025 24/01/2026 15 60 30 25/01/2026 24/01/2027 15 60 30 25/01/2027 24/01/2028 15 60 30 25/01/2028 24/01/2029 15 60 30 25/01/2029 24/01/2030 15 60 30 25/01/2030 24/01/2031 15 60 30 25/01/2031 24/01/2032 15 60 30 25/01/2032 24/01/2033 15 60 30 25/01/2033 24/01/2034 15 60 30 25/01/2034 24/01/2035 15 60 30 25/01/2035 31/10/2035 11 60 30

<colgroup><col><col span="4"><col span="2"><col span="16"></colgroup><tbody>
</tbody>

Last edited:

Replies
1
Views
500
Replies
3
Views
429
Replies
1
Views
401
Replies
3
Views
438
Replies
4
Views
457

1,203,757
Messages
6,057,164
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back