• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to calculate series of events using excel formulae

T

thakur

Member
Hi,


I am working on the leave data set..I need help tracking the unscheduled absenteeism in the data. How can I calculate the recurrence of unscheduled absenteeism keeping holidays in mind.


https://www.dropbox.com/s/phqi712yhobber4/Unsched%20data.xlsx


For eg: In the given data I need to calculate the person going on sick which is unscheduled followed by his regular off. I want to calculate the instances of the person taking unscheduled leaves followed by his regular offs..can you please help..


Thanks and Regards,

Thakur
 
Hi Thakur


So is it every instance of Vacation and Sick on the same line? One way;


=SUMPRODUCT(--(C3:HE3="Vaca")--(C3:HE3="Sick"))


Or are there more criteria?


Take care


Smallman
 
Hi Smallman,


No, The criteria here I want is to capture employee taking Sick, PB unscheduled leaves before or after his weekly OFF. Basicly to track howmany such instances are there for all the employees.


and if the Employee E1 is taking three days unscheduled leave for reasons like SICK it is considered to be 1 unscheduled leave followed by his Weekly off or Vacation. If you can provide me your insight into this. I will be grateful to you..


Thanks and Regards,

Thakur
 
Hi Thakur


Clearly the first time round you were not articulating yourself in a way where others might understand. Don't repeat that same piece of ambiguity a second time. It will be dealt with the same. What on earth is a weekly off? I can't see a code or a blank Week of cells with no data which would denote such a thing. Use the file you have uploaded to present some examples of the final output. I can't tell what you are after.


Take care


Smallman
 
Good day thakur

There is something very similar to what you are after at this link, if you download Narayans file you will see how it all comes together, you may need to alter parts to suit your needs

http://chandoo.org/forums/topic/extracting-sl-and-ul-1


Narayns file

https://www.dropbox.com/s/9up2h7pykg7dyhj/Attendance%20sheet.xlsx


Smallman has slapped your wrists for not making your needs clear in your original question, this is frustrating for those trying to help posters as they give an answer but the OP keeps moving the goal posts.


I think the problem is, as I have posted before, “newbies” to the forum post without really understanding the original problem, and it is only when they get an answer that they realise they should have asked more in the OQ.


Take your time, explain, in your words what you are trying to achieve, upload a file to show the data/problem/desired result.


If your question is related to the same workbook but is a different problem then start a new post, many members may have visited the OQ but will not realise you have buried a new question in it so will not revisit. This will limit to possible answers.


Finally, run very cold water over you wrists, it will take the sting out... :)


For those who where not born to the English language,.....but probably of more use to those who where born to the English language but are lazy in it's use... :)


Articulating

  • 1. Endowed with the power of speech.
  • 2. Composed of distinct, meaningful syllables or words.
  • 3. Expressing oneself easily in clear and effective language.
  • 4. Characterized by the use of clear, expressive language.


    Ambiguity


    • 1. Doubtfulness or uncertainty as regards interpretation.
      2. Something of doubtful meaning.


      Slapped Wrist


      1 Sharp slap to the delicate area just above the palm causing a stinging sensation.
      2 Inflicted by those who are frustrated by the owner of the wrists for not being Articulated
    • in their writings leading to Ambiguity.
 
Smallman, I think your tone is a little harsh where you say Don't repeat that same piece of ambiguity a second time. It will be dealt with the same. "


Telling someone not to repeat ambiguity seems a little harsh when likely they didn't realize that they were being ambiguous in the first place. It can be hard for someone who is a novice at Excel to get their question across well first or second time. Especially given we don't all have English as a first language.


The OP did in fact provide more information. It looks to me like they are trying to communicate their issue, although they obviously still haven't succeeded. But given a forum post is an ongoing conversation, I personally would have said "Hey, I still don't follow...can you provide a bit more info, and possibly upload a workbook with some callouts or shapes showing exactly what you want things to look like".
 
There was a duplicate post which appears to have been deleted by one of the moderators. The distance between these duplicates was two days apart. The post I wrote is out of context without this Duplication.


I was annoyed there was no new additional information in the third post by thakur and may have been a bit harsh, I did however ask for more information.
 
Sorry, I didn't realize that, Smallman.


It certainly can be frustrating trying to comprehend exactly what someone wants...especially when it invariably takes far longer to grasp the problem faced by the OP than it does to actually work out a solution.


On the other hand, it just seems like yesterday that I was an Excel newbie myself, badly explaining my issue to an excel uber-meister :) Got told off severely at Ozgrid once by the famous Dave Hawley! (Now he's up there in the great grid in the sky. And he certainly earnt his place there, with all the amazing effort he put into helping out).


On a tangent, what I personally hate is when you grasp the requirement straight away, post an amazing solution, but never ever even get a 'thanks' from an OP.
 
Hi Jeff


It is OK, it doesn’t look great without that other post. I bet BOB is who I have to thank for that : )


Yeah I don't always get it right on the communication front. I have to agree I would prefer to hear something like 'wrong' or 'NO' (my two favourite hates), than nothing at all. At least you have somewhere to go. I used to go at just about every problem that was out there till I started getting that soft sound of silence. Nothing is more frustrating than spending half a day cracking a problem only to get tumbleweed. I console myself with the thought that maybe people are finding it in years to come.


Dave Hawley was a friend of mine. I was very sad when I heard that he passed. I believe he had a fearsome reputation when protecting his forum in the early days so I guess you were in good company. He took his foot off and had some of the moderators do some of the harsh work and he was more of a fatherly figure towards the end of his life. I saw this softer side and I smile whenever I think of him.


It is very very quiet here at Chandoo central since the new user shut out over a week ago. I wonder when the new forum will be up and the historical data transferred across? Should bring a bit more life back into the forum.


Take it easy


Smallman
 
@ Smallman


Good day Sir


Just a bit of light harted fun , no malicious intention :)


I have said what I think the problem may be with "newbies" not understanding their needs and Excel experts getting frustrated by the question growing out of recognition from the OP.


I think Jeff touched on it with the Experts answering a "newbie" as if they knew what they wanted when many who ask are overwhelmed with Excel its self.


There seems to be more and more questions being posted from new members who have no experience of Excel but because they can switch a computer on some middle management who is only interested in cutting costs puts them in charge of the spreadsheets, and drowning in their panic and desire to keep their job, they come here for a lifebelt.


I do not think there is an answer to it other than softly chide when the question grows and come down like a tonne of bricks when a new question is posted in the OP.


And if some one hijacks a post.....slapped wrists. :)


Be quite and listen.....you will hear no tumbleweed.
 
You must log in or register to reply here.
Back
Top