Multiple Max Formulas

gavlink

New Member
Joined
Dec 9, 2019
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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?




Book1
ABCDEFGHIJKLMNOPQRSTU
2Pre-Pro STARTPre-Pro ENDOnsite STARTOnsite ENDRESOURCEPRE-PRO HOURSONSITE HOURS1/4/201/11/201/18/201/25/202/1/202/8/202/15/202/22/202/29/203/7/203/14/203/21/203/28/204/4/20
31/13/202/20/203/1/203/12/20Frank Rogers2461000.0042.4142.4142.4142.4142.4133.930.0055.5644.440.000.000.000.00
43/1/203/12/20Miles Scenic100#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
RangeFormula
I2:U2I2=H2+7
H3:U4H3=(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)
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,397
Maybe...

H3 copied across and down
=IFERROR(MAX(0,NETWORKDAYS(MAX(H$2,$A3),MIN((H$2+7),$B3))/NETWORKDAYS($A3,$B3)*$F3),0)+IFERROR(MAX(0,NETWORKDAYS(MAX(H$2,$C3),MIN((H$2+7),$D3)))/NETWORKDAYS($C3,$D3)*$G3,0)

M.
 

gavlink

New Member
Joined
Dec 9, 2019
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
Thanks Marcelo! this looks to have worked!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,339
Messages
5,547,360
Members
410,789
Latest member
cpolek83
Top