Posts by Kiwifinny

  • Formulas: Counting the number of cells that lie between 2 di

    If you wanted the total of the values or the average you can use an array formula.
    If the lower and upper limites are in B1 and B2 respectively and the data as per previous the formula could be
    =SUM((A1:A102&gt;B1)*(A1:A102<B2)*A1:A102) and then press control,alt, enter to make it an array formula. It will then be enclosed { }. To get the average divide by the sumproduct formula.

  • Work Days

    I am trying to work out the end date given a start date and number of work days required from that date.
    The networkdays function works for a start date and end date however I only have the start date and the days required.
    If I simply add the days required to the start date it does not subtract weekends or holidays naturally.
    Can anyone suggest a formula or method for achieving this.
    The inputs are start date, days required and holiday dates. Assume weekends are unavaiable.
    Many thanks in advance.

  • [Solved] Printing: Custom Footer

    Hi
    before my computer had a meltdown to rival Chernobyl, I had an add-in that automatically printed the spreadsheet name, tab, date etc without having to go into page set up each time. Suffice it to say I've lost it and it was very handy for reviewing print outs. Can anyone help please.
    PS: I wrote a great review!!!:P

  • [Solved] Printing: Named Ranges

    I have created a workbook using extensive names of ranges however now wish to show others what the names refer to. Is there a method for dumping them all on to one sheet or bottom of sheets with the name and the reference.
    Many Thanks
    John
    PS: This is the most useful site I have found.........ever!:)

  • [Solved] Conditional Formatting: Identifying Un-populated co

    Hi
    I'm trying to count the number of cells in a range that have a specific background colour.
    I'm developing a simple Project Plan and want to identify time planned by a coloured cell and as the project progresses populate the cell with time used.
    I can count the time used using the Countif function however I cannot calculate the total time planned, (Coloured un-populated cells)
    Can anyone please help?
    Many Thanks
    John

  • Control, forms

    I am trying to control a range of numbers using the forms, Control scroll bar so that someone using the spreadsheet can move the scroll bar and an associated number pops up in a defined cell. I can insert the control but don't know how to assign properties to it. Can anyone help?


    Many Thanks
    John