# Posts by Kiwifinny

Missed a bit.
=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.

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.

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.

Roy
many thanks once again, works well.
cheers from NZ

This may help as an example of using controls.
Cheers:wow:

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

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!:)

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)