Jump to content

subtract row values based on other columns


Recommended Posts

Hi there, I wonder if anyone can help me. See my attached Excel data file with a number of rows and columns.

I want to get the difference between the 2-row values for the "Batch Wait Time" column only when both below-listed conditions are true.

Here are the 2 conditions to subtract values between row values for the "Batch Wait Time" column;

  • (The "Time Order" column is "1" and the "Batch Order" column is value "x") - (The "Time Order" column is "2" and the "Batch Order" column is value "x+1")

These are the only conditions to subtract values between the 2 rows for the "Batch Wait Time" column.

Link to comment
Share on other sites

Hi Ibrahim,

I calculated the different in Batch Wait Times with a couple of steps.

First, I created a copy of the original data, but filtered to where [Time Order] = 1 or 2, i.e. where we have Batch Wait Time values.

I then pivoted this table, to give a list of Batch IDs as the Row IDs, then the minimum Batch Wait Time and maximum Batch Wait Times as the columns, for each Batch ID:

Screenshot2023-02-24at10_24_20.png.3ee2faf5be8f50c6cf7dd687ecc37b95.pngAt this point then, we have a table with two wait times, and it's a case of simply adding in a Calculated Column to calculate the difference in time between those two values, as shown in the table here:

Screenshot2023-02-24at10_24_37.thumb.png.0cf6b26c07d96d1e3899764d88a05208.pngHope that helps!

Link to comment
Share on other sites

Hi Astrid, thank you so much for the effort, and really grateful. The only issue with this solution is the subtraction between those columns is based on the same Batchid whereas we need to subtract those 2 columns when the Batch IDs are different, as in current and previous BatchIDs to be precise. This way we can calculate the actual wait time between the 2 consecutive batches running. Here is a new simplified data file as my previous Excel file has a lot of columns and is confusing.

Maybe I couldn't explain it better due to my English but simply we want to subtract 2 rows under the RE_805 Wait Time column when the Batch Order numbers 1 and 2 but the Batch order numbers will also be different so here is the simplified version;

(Value in "RE_805 WaitTime" when "Batch Order" = 10 and "Time Order = 1 ) - (the value in "RE_805 WaitTime" when "Batch Order" = 9 and "Time Order = 2). This trigger condition will continue on to the other rows and calculate the Wait Time between the 2 consecutive Batches. maybe there is no solution to what I am looking for but this is what we want o achieve if it is possible for our Production Bastch Report to analyze and see how much time we are losing in each batch which is called "Wait Time" or simply dead time).

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...