Excel formula help, any gurus out there?

Discussion in 'Gaming and Software' started by Rod924, May 23, 2011.

Welcome to the Army Rumour Service, ARRSE

The UK's largest and busiest UNofficial military website.

The heart of the site is the forum area, including:

  1. Rod924

    Rod924 LE Reviewer

    Not sure this is posted in the right forum; mods please move if required.

    I have put together a maintenance contract spreadsheet and I am struggling to make it work better. Details are

    Column a - contract start date
    B - contract end date
    C - "+B-30" (Reminder send out date)
    D - "date"
    E - "=if(d>c,"send reminder","")
    F - date reminder sent
    G - "+f+14" (To give follow up call)
    H - "=if(d>g,"follow up call","pending")

    Now, whilst it works, its a bit too long winded, column wise. But, whilst info is known for columns A through d, column f only has data when required/inputted, so at the moment, g and h are copy and pasted once info given in f (value return otherwise)

    Any ideas how to make this easier??

    I would like to have E, reporting all - send reminder, pending and follow up call"

    Help appreciated
  2. I recently posted a similar help thread and got a fantastic response, Although I am unable to help would it be possible for the MOD's to form a thread with all these little help snippets put in once place?
  3. I was wondering about how to do this myself, so I have just bothered my arse to figure it out.

    You need something like this

    =IF(D>=G,"follow up call",(IF(D>=F,"pending",IF(D>=C,"send reminder",""))))

    Basically it starts with the latest event and works backwards. l also used >= (greater than or equal to) as it seems to make more sense.

    This makes me geek doesn't it?
  4. You could use conditional formatting on the contract end date to give visual clues as to whether a reminder is due.

    Post the sheet or an extract of it and I can cut it around for you.

  5. Yes mate. I'm an IT tech, and I even I can't be arsed with all that s**t.
  6. Rod924

    Rod924 LE Reviewer

    As I don't want to reinvent the wheel, asking folk that know to save time. Sadly, I can only use my laptop on safe mode
    , so unable to post a screen print of the spreadsheet. But the details above outline what it looks like and what I am trying to achieve.

    So, anyone have a simple solution?
  7. Bit of a sample here.

    Option 1 - Rows 2 - 6
    Your 'working date' is entered in a column, the reminder & followup are shown in different columns

    Option 2 - Rows 8 - 13
    Your 'working date' is always 'today' and can be embedded in the formalae to save a column, the reminder & followup are shown in different columns
    (There are both a 'today()' and 'now()' function in Excel which may vary between versions)

    Option 3 - Rows 8 - 13
    Your 'working date' is always 'today', the reminder & followup are combined into one
    (This is getting to a pretty complex formula so to be sure I have set the evaluations in the right order will need further test figures)

    I have made the use of >= whereever I think appropriate.
    I have assumed contract end date is one year on and used a +365 formula, you may need to enter a specific date
    today() may need testing to ensure it works correctly with your Excel version, and that it always refreshes to then day that the spreadsheet is being viewed

  8. Oops ...
    Think that last formula was wrong - if status is combined you may want 'Reminder sent / pending followup'

    ...and edited to actualy use the revised formula!
  9. Rod924

    Rod924 LE Reviewer

    Tommilka, many,many thanks for your efforts and others too.