Sorry its taken so long to respond but I've been in the field/on holiday.
Unfortunately I don't have access to the spreadsheets at the moment but here is the basic method:
1) Set up a table for %full depth based on a pipe diameter of 1. Depth increments of 0.01 are what I traditionaly use.
2) For the depth calculate the water area and wetted perimeter. You'll need to dig out your geometry formulas to find the equations. (Don't forget that the formulas will change once you are over half full.)
3) Calculate hydrualic radii for each depth.
4) Calculate the % Velocity based on the full flow velocity. Since its a ratio, the slope factor drops out.
5) Calculate the % Capacity based on the full flow capacity. Make this column the column immediately to the left of the %Velocity column. (% Capacity = % Velocity * Area which you already calculated.)
6) Use Excel's VLookUp function to search the table for the closest % Capacity and return the corresponding % Velocity.
7) Use your full flow velocity and the the % velocity to determine your partial flow velocity.
You end up with a Workbook that has 1 Spreadsheet containing the "Partial Flow Table" and 1 Spreadsheet where you do your calculations.
You can always refine your spreadsheet further by setting up another Table containing the standard pipe characteristics you use. Let the first column of that table be the size & material of pipe with the other columns the diameter(s), area, etc. Then name your list of pipes (Insert Name Define). Use Data Validation to use your named list of pipes as a selection list in your spreadsheet. This forces you to pick from a list of known pipes. You can then use VLookUp to pull the pipe properties from your pipe table automatically.
David
David Dietrich
KMK Consultants Ltd
Windsor, Ontario, Canada