+ Post New Thread
Results 1 to 10 of 10
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 ...
  1. #1
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    2,988
    Thank Post
    287
    Thanked 270 Times in 194 Posts
    Rep Power
    116

    Excel formula help

    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.

    Would anyone be able to help?

  2. #2
    rdk
    rdk is offline

    Join Date
    Sep 2008
    Posts
    128
    Thank Post
    2
    Thanked 16 Times in 15 Posts
    Rep Power
    31
    How about this:

    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.

  3. #3

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    Sorry, I really don't understand what you are trying to achieve...

  4. #4

    Join Date
    Jun 2008
    Location
    Northants
    Posts
    86
    Thank Post
    16
    Thanked 17 Times in 17 Posts
    Rep Power
    15
    What @rdk said, then Google nested if statements, that should help you out. (and I didn't 100% understand what you are trying to achieve either)

  5. #5
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    2,988
    Thank Post
    287
    Thanked 270 Times in 194 Posts
    Rep Power
    116
    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.

  6. #6
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    2,988
    Thank Post
    287
    Thanked 270 Times in 194 Posts
    Rep Power
    116
    **BUMP**

    If anyone can help me out i would be very grateful. This sort of thing can be used for many things in school though.

  7. #7

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    I am still getting confused by your 1s and 2s...

    If they do something wrong twice show 1
    If they do something once show 2...

    Excel can't "notify" you about anything it can only calculate from input data and then show the results using formulae.

    Can you explain what data is going to be input and where and then how this relates to what answers/info you need to get out?

  8. #8
    Mako's Avatar
    Join Date
    Apr 2009
    Location
    North Yorkshire
    Posts
    431
    Thank Post
    50
    Thanked 107 Times in 81 Posts
    Rep Power
    71
    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...
    Attached Files Attached Files
    Last edited by Mako; 4th May 2012 at 02:39 PM.

  9. Thanks to Mako from:

    timbo343 (4th May 2012)

  10. #9
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    2,988
    Thank Post
    287
    Thanked 270 Times in 194 Posts
    Rep Power
    116
    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.
    Attached Files Attached Files

  11. #10
    timbo343's Avatar
    Join Date
    Dec 2005
    Location
    Leeds/York area, North Yorkshire
    Posts
    2,988
    Thank Post
    287
    Thanked 270 Times in 194 Posts
    Rep Power
    116
    @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.

SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 06:18 AM
  2. Excel Formula Help
    By timbo343 in forum Office Software
    Replies: 3
    Last Post: 6th May 2010, 09:42 AM
  3. HELP - Excel Formula - HELP
    By princessbucki in forum Office Software
    Replies: 7
    Last Post: 19th March 2010, 04:19 PM
  4. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 05:31 PM
  5. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •