Excel Help

Discussion in 'SMB' started by houst69, Jan 11, 2019.

  1. houst69

    houst69 Striker

    I need cells that go green that are within a date range. For example between 01 Apr 18 and 31 Mar 19. I am struggling to find info online for this.

    Once these dates go green (there are 16 different tests) I need the completion rate at the end so if dates are entered in the column it gives a final %

    Anybody help?
     
  2. TopCat

    TopCat Striker

    Conditonal formatting
    Cell values between 43191 & 43555

    Not sure what you mean on the 2nd bit
     
  3. Always needing help you
     
  4. TopCat

    TopCat Striker

    If you mean you want to show the percentage of tests completed then a simple =COUNTA() function should count all the cells with entries in them and you can divide that by 16

    So, assuming your cell range was B1:Q1, in cell R1 you could put the fomula =COUNTA(B1:Q1)/16
    Format the cell as a % and there you have it
     
  5. pavarotti1980

    pavarotti1980 Midfield

    Microsoft Office Specialist (MOS) Certification | Microsoft Learning
     
  6. David M

    David M Winger

    If you want to count cells of a certain colour then you'll need a macro (although I suppose it could be done by other means). I have a sheet that does this.
     
    shepherd88 likes this.
  7. johnnyat87

    johnnyat87 Winger

    ASAP utilities is a useful tool to download and add to excel for this sort of thing mate.
     
  8. I assume that he's on a wind up. Nobody could set up a web development business and be this feckless.
    It's like me setting myself up as a mechanic and not knowing how to open the bonnet of the car. Or what the bonnet is called.
    The only other option is that he's blagged a role at work that is totally incapable of doing.
     
  9. jjarra_mackem

    jjarra_mackem Midfield

  10. houst69

    houst69 Striker

    Cheers this worked
     
  11. Kevj

    Kevj Striker

    I did something similar without using a macro but a ‘cheat’ column.

    Set the conditional formatting as suggested above, then include an additional column to the right of every one which you wish to measure completion. In the cell to the side, include a countif formula (possibly sumif?) it return the letter ‘x’ when the criteria is met.

    Then in the cell where you want to measure progress, add countif formula to the cheat column for the letter ‘x’. Then hide the cheat column.

    It’s messy, but it works if you can’t use macros.
     
  12. Mantobar

    Mantobar Midfield

    Few years ago my boss highlighted some cells then proceeded to get his calculater out to add the numbers together :eek:
     
    Stevie Freestein II and TopCat like this.
  13. jjarra_mackem

    jjarra_mackem Midfield

    Worth looking into some macro skills. If you end up with large worksheets they can be a real timesaver
     
    TopCat likes this.
  14. peil

    peil Striker Contributor

    I've seen people do that and then complain the numbers don't seem right cos the missed a row.
    Should be a disciplinary imo, can you imagine getting a job as a CNC operator and then hand cutting shit with a hacksaw and asking the SMB how to get it to line up straight.
     
    Stevie Freestein II likes this.
  15. TopCat

    TopCat Striker

    I've seen that happen before. People using Excel and asking if anyone has a calculator they can borrow
     
    RossTheNinja likes this.
  16. Or typing in a formula to add each cell one by one
    Eg = A1+A2+A3...etc. Just look at the bottom of the bloody screen.
     

Share This Page