Excel formula for leaves

[komalbatraa]

Please help me with the excel formula for below mentioned problem:-

I have made an excel sheet in which i have a column of late reporting in office
i want to cal late reporting in another column if
-there are 3 late reporting then half day will be counted
-for 4th 5th and 6th late reporting in a month one leave and a half day will be counted.
-for 7th 8th and 9th late reporting two and a half day will be counted
and so on
(3 days slab, +1 will be counted)

all this leaves will be deducted from the number of days present.

 

[Robert Mika]

Hi and welcome to the board.
Could you post some data?

 

[komalbatraa]

=if(l17<3,0,0)=if(l17=3,0.5,0)=if(and(l17>=4,l17<=6),1.5,0)=if(and(l17>=7,l17<=9),2.5,0)=if(and(l17>=10,l17<=12),3.5,0)=if(and(l17>=13,l17<=15),4.5,0)

plz guide this formula is not working
its returning me "false"
i want it to return value

 

[Robert Mika]

You can not nest IF in this way- putting the quals sign after whole function.
If you need more that one IF the false statment of the formula should be the first part of the IF.
Try this:
=IF(L17<3,0,IF(L17=3,0.5,IF(AND(L17>=4,L17<=6),1.5,IF(AND(L17>=7,L17<=9),2.5,IF(AND(L17>=10,L17<=12),3.5,IF(AND(L17>=13,L17<=15),4.5,0))))))

 

[taurean]

Komal,
You will have to consider many if cases which may prove unwieldy to edit. Instead try this simpler formula:

=IF(L17>=3,CEILING(L17/3,1)-0.5,0)

 

[komalbatraa]

Thankyou sir

You can not nest IF in this way- putting the quals sign after whole function.
If you need more that one IF the false statment of the formula should be the first part of the IF.
Try this:
=IF(L17<3,0,IF(L17=3,0.5,IF(AND(L17>=4,L17<=6),1.5,IF(AND(L17>=7,L17<=9),2.5,IF(AND(L17>=10,L17<=12),3.5,IF(AND(L17>=13,L17<=15),4.5,0))))))