Multiple IF and MOD statements unsure about parentheses

[cookieof89]

Hi there,

I have been working on this code for two days now and i just cant quite crack it.

Basically what it does is calculate how many work days and hours a job will take.

Seeing as a days production is 9 hours i want it to add 0.6 to any remainder that is between 0.4 and 0.8 and add 1.2 to anything that has a remainder of 0.8 or above.

It is also written to display nothing if there is no value in C4.

Formula:

=IF(C4>0,(IF((AND((MOD((I4/(J4*M4)),9))>0.4),((MOD((I4/(J4*M4)),9))<0.8))),(((I4/(J4*M4))/9)+0.6),(IF(((MOD(I4/(J4*M4)),9)>=0.8),(((I4/(J4*M4))/9)+1.2),((I4/(J4*M4))/9)))),””)

Using:

I4 = 1000
J4 = 2
M4 = 133

Result should end up being 0.42 or 10 hours, however i want this to roll over to 1 days, 1 hours

I think i've just stuffed up the brackets somewhere

Any help greatly appreciated, or suggestions

Thanks

Cookie

[Bob Phillips]

I don't get the logic, but this formula gets a return

=IF(C4>0,IF(AND(MOD(I4/(J4*M4),9)>0.4,MOD(I4/(J4*M4),9)<0.8),(I4/(J4*M4)/9)+0.6,
IF(MOD(I4/(J4*M4),9)>=0.8,(I4/(J4*M4)/9)+1.2,I4/(J4*M4)/9)),"")

[Krishnakumar]

Hi,


=IF(C4>0,IF(I4/(J4*M4)>0.4,(I4/(J4*M4)/9)+LOOKUP(MOD(I4/(J4*M4),9),{0,0.4,0.8},{0,0.6,1.2}),I4/(J4*M4)),"")