Help Creating a Formula

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]
 
forget your detail for a moment and rephrase the question in much more simple terms eg apples and pears with delivery, sales and sell by data
 
Upvote 0
Ok i will try

I have 100 planks that need to be cut on a machine in location A. Today we only cut 50 plans (tomorrow we will cut the rest) and those 50 plans are now showing available to work on in location B.

What i would like is to have the formula only show planks available in location B once all 100 planks have been finished cutting at location A. Once the 100th plank is cut in location A i want it then to show in location B.

is this more helpfull?
 
Upvote 0
[TABLE="width: 734"]
<colgroup><col span="4"><col span="2"><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]order[/TD]
[TD]order date[/TD]
[TD]start date[/TD]
[TD]cut[/TD]
[TD]outstanding[/TD]
[TD]available?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]planks[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]01/08/2016[/TD]
[TD="align: right"]02/08/2016[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]41[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]widgets[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]01/08/2016[/TD]
[TD="align: right"]02/08/2016[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]no obtained by[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]=IF(H14>0,"no","yes")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top