Hi everyone,
I have finally figured out why my excel formula was acting up. Each time I convert the range into a table it affects the results of my cells which have formulas.
Below is the formula you can insert in cell L5 in the screenshot:
=IF(OR($D5>EOMONTH(L$4,0),AND($E5<L$4,$E5>0)),0,(($F5/$G5)/DAY(EOMONTH(L$4,0)))*(IF(AND($H5>0,$I5>0),((MAX(0,MIN($H5-1,EOMONTH(L$4,0))-MAX($D5,L$4)+1)+(MAX(0,IF($E5>0,MIN($E5,EOMONTH(L$4,0)),EOMONTH(L$4,0))-MAX(L$4,$I5)+1)))),(MAX(0,IF($E5>0,MIN($E5,EOMONTH(L$4,0)),EOMONTH(L$4,0))-MAX(L$4,$D5)+1)))))
Please take note that the screenshot is of the work BEFORE being converted to table. As soon as I convert it to a table 'all hell break loose' in the green coloured columns which have the formulas.
THanks and any suggestions will be gratefully accepted.
I have finally figured out why my excel formula was acting up. Each time I convert the range into a table it affects the results of my cells which have formulas.
Below is the formula you can insert in cell L5 in the screenshot:
=IF(OR($D5>EOMONTH(L$4,0),AND($E5<L$4,$E5>0)),0,(($F5/$G5)/DAY(EOMONTH(L$4,0)))*(IF(AND($H5>0,$I5>0),((MAX(0,MIN($H5-1,EOMONTH(L$4,0))-MAX($D5,L$4)+1)+(MAX(0,IF($E5>0,MIN($E5,EOMONTH(L$4,0)),EOMONTH(L$4,0))-MAX(L$4,$I5)+1)))),(MAX(0,IF($E5>0,MIN($E5,EOMONTH(L$4,0)),EOMONTH(L$4,0))-MAX(L$4,$D5)+1)))))
Please take note that the screenshot is of the work BEFORE being converted to table. As soon as I convert it to a table 'all hell break loose' in the green coloured columns which have the formulas.
THanks and any suggestions will be gratefully accepted.