MaureiraMat
New Member
- Joined
- Jun 7, 2016
- Messages
- 44
Hello,
I need help creating a formula. I have no idea where to start.
I would like to create what i think would be an IF formula.The formula I am using right now is this =IF(R2 <=0,(O2+N2)/I2 *(I2-J2),0) <--- this formula is for inv ontime in B
=IF(R2>=0,(O2+N2)/I2*(I2-J2),0) <--- this formula is for inv late
The order that I have past the data is how it is on the workbook. ( INV LATE is : A ) ( INV ONTIME : B ) so on and so forth.
How do I make excel count a quantity available only once the full quantity has been completed. As you can see from the data there is a column that says next resource. I want excel to only show that the "job" is in the next recourse only when the full qty has been completed.
I am sorry if i did not explain this well. If you cant understand what I am asking i will try to explain more.
Thank you.
[TABLE="width: 1833"]
<tbody>[TR]
[TD="class: xl65, width: 141"]Inv late[/TD]
[TD="class: xl66, width: 197"]Inv ontime[/TD]
[TD="class: xl69, width: 64"]PM[/TD]
[TD="class: xl69, width: 64"]Resource ID[/TD]
[TD="class: xl69, width: 64"]Part ID[/TD]
[TD="class: xl69, width: 64"]Description[/TD]
[TD="class: xl69, width: 102"]WO ID[/TD]
[TD="class: xl69, width: 64"]Seq #[/TD]
[TD="class: xl69, width: 64"]Qty[/TD]
[TD="class: xl69, width: 64"]Qty Comp[/TD]
[TD="class: xl69, width: 64"]Qty Avail[/TD]
[TD="class: xl69, width: 64"]Start Date[/TD]
[TD="class: xl69, width: 64"]Finish Date[/TD]
[TD="class: xl69, width: 72"]Set Hrs[/TD]
[TD="class: xl69, width: 108"]Run Hrs[/TD]
[TD="class: xl69, width: 64"]Set[/TD]
[TD="class: xl69, width: 93"]WO Due Date[/TD]
[TD="class: xl69, width: 64"]Days Late[/TD]
[TD="class: xl69, width: 96"]Days Aged[/TD]
[TD="class: xl69, width: 64"]Prev Resource[/TD]
[TD="class: xl69, width: 64"]Next Resource[/TD]
[TD="class: xl69, width: 64"]Ops Left[/TD]
[TD="class: xl69, width: 64"]Comments[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1833"]
<colgroup><col><col><col span="4"><col><col span="6"><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]9.73[/TD]
[TD][/TD]
[TD]DEBURR[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-111606/1.0[/TD]
[TD]90[/TD]
[TD]115[/TD]
[TD]0[/TD]
[TD]115[/TD]
[TD="align: right"]9/6/2016[/TD]
[TD="align: right"]9/6/2016[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]N[/TD]
[TD="align: right"]10/25/2016[/TD]
[TD]-1[/TD]
[TD]2[/TD]
[TD]ETCHING[/TD]
[TD]RECV INSP[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]6.92[/TD]
[TD]U[/TD]
[TD]ETCHING[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-112030/1.0[/TD]
[TD]80[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]9/23/2016[/TD]
[TD="align: right"]9/23/2016[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]N[/TD]
[TD="align: right"]11/17/2016[/TD]
[TD]-6[/TD]
[TD]2[/TD]
[TD]INT-100[/TD]
[TD]S-HT-CRES[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]20.5[/TD]
[TD][/TD]
[TD]INT-100[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-112030/1.0[/TD]
[TD]40[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD="align: right"]9/16/2016[/TD]
[TD="align: right"]9/19/2016[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]N[/TD]
[TD="align: right"]11/17/2016[/TD]
[TD]-6[/TD]
[TD]2[/TD]
[TD]RECV INSP[/TD]
[TD]ETCHING[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]10.25[/TD]
[TD][/TD]
[TD]INT-100[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-112030/1.0[/TD]
[TD]60[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD="align: right"]9/21/2016[/TD]
[TD="align: right"]9/22/2016[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]N[/TD]
[TD="align: right"]11/17/2016[/TD]
[TD]-6[/TD]
[TD]2[/TD]
[TD]INT-100[/TD]
[TD]INSP[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need help creating a formula. I have no idea where to start.
I would like to create what i think would be an IF formula.The formula I am using right now is this =IF(R2 <=0,(O2+N2)/I2 *(I2-J2),0) <--- this formula is for inv ontime in B
=IF(R2>=0,(O2+N2)/I2*(I2-J2),0) <--- this formula is for inv late
The order that I have past the data is how it is on the workbook. ( INV LATE is : A ) ( INV ONTIME : B ) so on and so forth.
How do I make excel count a quantity available only once the full quantity has been completed. As you can see from the data there is a column that says next resource. I want excel to only show that the "job" is in the next recourse only when the full qty has been completed.
I am sorry if i did not explain this well. If you cant understand what I am asking i will try to explain more.
Thank you.
[TABLE="width: 1833"]
<tbody>[TR]
[TD="class: xl65, width: 141"]Inv late[/TD]
[TD="class: xl66, width: 197"]Inv ontime[/TD]
[TD="class: xl69, width: 64"]PM[/TD]
[TD="class: xl69, width: 64"]Resource ID[/TD]
[TD="class: xl69, width: 64"]Part ID[/TD]
[TD="class: xl69, width: 64"]Description[/TD]
[TD="class: xl69, width: 102"]WO ID[/TD]
[TD="class: xl69, width: 64"]Seq #[/TD]
[TD="class: xl69, width: 64"]Qty[/TD]
[TD="class: xl69, width: 64"]Qty Comp[/TD]
[TD="class: xl69, width: 64"]Qty Avail[/TD]
[TD="class: xl69, width: 64"]Start Date[/TD]
[TD="class: xl69, width: 64"]Finish Date[/TD]
[TD="class: xl69, width: 72"]Set Hrs[/TD]
[TD="class: xl69, width: 108"]Run Hrs[/TD]
[TD="class: xl69, width: 64"]Set[/TD]
[TD="class: xl69, width: 93"]WO Due Date[/TD]
[TD="class: xl69, width: 64"]Days Late[/TD]
[TD="class: xl69, width: 96"]Days Aged[/TD]
[TD="class: xl69, width: 64"]Prev Resource[/TD]
[TD="class: xl69, width: 64"]Next Resource[/TD]
[TD="class: xl69, width: 64"]Ops Left[/TD]
[TD="class: xl69, width: 64"]Comments[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1833"]
<colgroup><col><col><col span="4"><col><col span="6"><col><col><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]0[/TD]
[TD]9.73[/TD]
[TD][/TD]
[TD]DEBURR[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-111606/1.0[/TD]
[TD]90[/TD]
[TD]115[/TD]
[TD]0[/TD]
[TD]115[/TD]
[TD="align: right"]9/6/2016[/TD]
[TD="align: right"]9/6/2016[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]N[/TD]
[TD="align: right"]10/25/2016[/TD]
[TD]-1[/TD]
[TD]2[/TD]
[TD]ETCHING[/TD]
[TD]RECV INSP[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]6.92[/TD]
[TD]U[/TD]
[TD]ETCHING[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-112030/1.0[/TD]
[TD]80[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD="align: right"]9/23/2016[/TD]
[TD="align: right"]9/23/2016[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]N[/TD]
[TD="align: right"]11/17/2016[/TD]
[TD]-6[/TD]
[TD]2[/TD]
[TD]INT-100[/TD]
[TD]S-HT-CRES[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]20.5[/TD]
[TD][/TD]
[TD]INT-100[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-112030/1.0[/TD]
[TD]40[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD="align: right"]9/16/2016[/TD]
[TD="align: right"]9/19/2016[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]N[/TD]
[TD="align: right"]11/17/2016[/TD]
[TD]-6[/TD]
[TD]2[/TD]
[TD]RECV INSP[/TD]
[TD]ETCHING[/TD]
[TD]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]10.25[/TD]
[TD][/TD]
[TD]INT-100[/TD]
[TD]161A2[/TD]
[TD]BOLT - APEX SIDE STRUT[/TD]
[TD]WO-112030/1.0[/TD]
[TD]60[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD="align: right"]9/21/2016[/TD]
[TD="align: right"]9/22/2016[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]N[/TD]
[TD="align: right"]11/17/2016[/TD]
[TD]-6[/TD]
[TD]2[/TD]
[TD]INT-100[/TD]
[TD]INSP[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]