Working on distributing two sets of values across workdays. Have the below formula which works well when I have a full set of data H3 -> U3
However would like to also work when only one set of data is present. see H4 -> U4 results in error as its missing data in A4 and B4.
Any ideas?
However would like to also work when only one set of data is present. see H4 -> U4 results in error as its missing data in A4 and B4.
Any ideas?
Book1 | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
2 | Pre-Pro START | Pre-Pro END | Onsite START | Onsite END | RESOURCE | PRE-PRO HOURS | ONSITE HOURS | 1/4/20 | 1/11/20 | 1/18/20 | 1/25/20 | 2/1/20 | 2/8/20 | 2/15/20 | 2/22/20 | 2/29/20 | 3/7/20 | 3/14/20 | 3/21/20 | 3/28/20 | 4/4/20 | ||
3 | 1/13/20 | 2/20/20 | 3/1/20 | 3/12/20 | Frank Rogers | 246 | 100 | 0.00 | 42.41 | 42.41 | 42.41 | 42.41 | 42.41 | 33.93 | 0.00 | 55.56 | 44.44 | 0.00 | 0.00 | 0.00 | 0.00 | ||
4 | 3/1/20 | 3/12/20 | Miles Scenic | 100 | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | #DIV/0! | |||||
Copy of MASTER GRID |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2:U2 | I2 | =H2+7 |
H3:U4 | H3 | =(MAX(0,NETWORKDAYS(MAX(H$2,$A3),MIN((H$2+7),$B3)))/NETWORKDAYS($A3,$B3)*$F3)+(MAX(0,NETWORKDAYS(MAX(H$2,$C3),MIN((H$2+7),$D3)))/NETWORKDAYS($C3,$D3)*$G3) |