User Profile
Giancarlo1977
Frequent Visitor
Joined 2 years ago
User Widgets
Contributions
Re: Calculate new value based on previous on same colum
Hi, Thanks for your reply but unfortunately the formula is not working for me. When I run it I have the following value: Week String per week AV UR RTG Planned Repair New UR 1 42 40 5 35 5 2 30 40 5 35 0 0 3 24 40 5 35 0 5 4 18 40 5 35 0 5 5 44 40 5 35 5 5 6 92 40 5 35 5 0 The value hilighted in bolt are not correct (based on what I am looking for) The Planned Repair in week 5 and week 6 should be 0 because the New UR from Week 2 till Week 6 should always be 0 since I have repaired all 5 in week 1. So since in week 1 my Planned Repair is 5 my new UR will be 0 and RTG will be 40. So the new Planned Repair formula for the following weeks should consider what happened in previous weeks I hope this is clear and I appreciate your helpCalculate new value based on previous on same colum
Hi, I am running a project and what I am trying to do is to calculate a Planned Repair based on StringW and UR (Under Repair) StringW = Demand which is different every week AV = Current Total Capability (coming from system) UR = Current Under Repair (coming from system) RTG = Ready to Go : AV-UR StringWx = Is my input which is the weekly demand AV = Is a fix number and it is my current total capability UR_x = After the starting number and based on Planned Repair then this number will be recalculated and change over week RTG_x = After the starting number and based on Planned Repair this number will be recalculated and change over week I am doing this in excel by creating several column for each week like the example below ToolCode String W1 String W2 String W3 String W4 AV UR_1 RTG_1 Planned Repair_1 UR_2 RTG_2 Planned Repair_2 UR_3 RTG_3 Planned Repair_3 UR_4 RTG_4 Planned Repair_4 aaa 40 38 42 44 41 3 38 2 1 40 0 1 40 1 0 41 0 The formula that I am using in excel to calculate the Planned Repair_1 is: =IF(UR_1=0,0,IF(RTG_1>=StringW1,0,IF(StringW1>(UR_1+RTG_1),UR_1,MIN(UR_1,ABS(RTG_1-StringW1))))) For the following week I have recalculated the UR_2 = UR_1-Planner Repair_1 and RTG_2 = AV-UR_1. Therefore the new Planned Repair_2 will use the same formual but it will consider UR_2 and RTG_2 Planner Repair_2 = =IF(UR_2=0,0,IF(RTG_2>=StringW2,0,IF(StringW2>(UR_2+RTG_2),UR_2,MIN(UR_2,ABS(RTG_2-StringW2))))) And like this for all weeks in different column (which is difficult to see) I am not good in excel so that is the only way I found I would like to have same results in 1 column in Power BI since my project is automated in PowerBI so if I can figure out the correct way it will by automatically refreshed giving me everytime the new plan and it is easier to monitor So I am trying to find a way to calculate the following columns: Planned Repair New UR New RTG ToolCode WeekName String per week AV UR_1 RTG_1 Planned Repair (Calculated Column) New UR(Calculated Column) New RTG(Calculated Column) aaaa 1 40 41 3 38 2 3 38 aaaa 2 38 41 3 38 0 1 40 aaaa 3 42 41 3 38 1 1 40 aaaa 4 44 41 3 38 0 0 41 Really appreciate all your support ThanksCumulative formula inside IF formula
Hi Team, I am looking for a formula that can help me in planning my repair. I have an if function but eventually I need to reapply the calculation based on the new value coming after the previous calculation. The below screen can explain better what I am looking for This is my main formula. But the value of UR should be re-evaluate based on how many I repaired the previous week My Result should be in the Cumulative Under Repair for same Concatenate Tool Code (which is wrong now!) For 19 May = 2 (just the qty I neew based on String per week) 26 May = 0 (no demand so no need to repair) 2 Jun = 1 (because I have already repaired 2 19 May so my new count of UR is 1 and RTG is 40 9 Jun = 0 (because I have repaired all UR already) 21 July = 0 (because I have repaired all UR already) I am struggling how to include this cumulative in the formula. Anybody can help? Appreciate all your support Regards
