Office Software Thread, Excel formula help in Technical; I need to create an excel sheet that will inform me to do action something within a given time period ...
I need to create an excel sheet that will inform me to do action something within a given time period but cannot for the life of me where to start.
In Column A would be the date.
In Column B would a number i inputted, say 1 -5.
What i want is a formula that would look up the last 2 weeks from today and add the values together and make sure they are equal or greater than 2. If this was the case, i would need excel to notify me. I think this can be done with an IF formula but its the next bit that confuses me.
If the value was equal to 2 or more, i would want another formula that will monitor the next 2 weeks from said date, so if i inputted a 1 on the 1/04/12 and again on the 14/04/12, excel would need to work out 2 weeks from the 14/04/12 and monitor these dates so if i inputted a number in the next 2 weeks i would then excel would notify me that this has happened. If something happened out of this 2 week period, then i would need excel to igore it.
The trouble is, this is to happen all the way through the year. It seems a massive and a complex formula to me.
Is this been too complex for excel and should i be looking at using a database. I have just suggested a normal calendar and work out 2 weeks from here.
Col A - dates (I'm assuming every day is there)
Col B - values you input
Col C: starting at C14 (ie 2 weeks, starting today) formula = sum(B1:B14)
Then drag the formula in Col C down and you have the sum every day for the previous 14 days.
Then in column D you could do something like =if(C14 > 2 then......)
Not sure if this is exactly what you want, as it works out the previous 14 days for every day, not in blocks of 2 weeks.
i understand... i was trying to word it without been too obvious what it was regarding, but ill try again.
If someone does something wrong in a 2 week period, it needs to be logged, so, if someone completed something wrong at least twice in a two week period, i need excel to display "letter 1". Once this has happened, we need to monitor the next 2 weeks. If it happens at least once within the 2 week monitor period, i need excel to notify me by displaying "letter 2"
Hope this makes further sense.
@rdk, i had something like this but wasn't quite working as i wanted it to
Last edited by timbo343; 3rd May 2012 at 03:20 PM.
I've been staring at this thread for about two hours now and still have no clear idea of what you're trying to achieve.
This is the best and most rudimentary (I'm by no means an excel wizard) thing I could come up with.
See attached file.
Enter the date of an incident.
Column C will do a simple calculation against today's date and the entered date of the incident.
Column D will see if the difference in days is less than or equal to 14; if so, "Yes", if not, "No".
Column E has a drop down "Yes" or "No" option for you to select if another incident has occurred for that person.
Column F calculates the score, a "Yes" equals 1 point, a "No" is no points.
Column G has some conditional formatting to let you know of any scores equal to 2.
I can't think of anything other than that, and even now I don't know if I've over-complicated or over-simplified it, or even missed the mark altogether.
There must be an excel buff around here somewhere...
The 1s, 2s, 3s and so on up to 5 would be how many times someone would do something wrong, ie, we have 5 periods a day so the maximum someone can get it wrong is 5 times.
Its hard to explain it, its a lot better to show what i mean.
The attached is a little something i started.
Column A has the week number
Column B has the Day
Column C has the Date
Column D has the user's name and how many times the user has made a mistake.
So, if the user completes the regsiter wrong twice in a two week period, excel will display letter 1 for week 1 and 2 and if they fill it out Now, i need excel to show a notification that this user is in a warning period and if they fill out the register wrong again in the next 2 weeks, excel needs to show this with letter 2. The part im stuck at is getting excel to monitor the next 2 weeks and report if the same user makes a mistake, then excel would need to show letter 2. If the 2 weeks passes, and no mistake has been made, then the whole process needs to start again.
Im really sorry if people have been able to understand what im on about.
Imagine a student has forgetten their password and you need to log it each time they forget it.
@Mako thank you for the attachment, i shall have a look at this over the weekend and see what i can get out of it, its on the right route of what im looking for.
LinkBack URL
About LinkBacks
