9 Replies Latest reply on Oct 6, 2017 5:12 AM by sudhanshu shukla

# Taking average of two values in differnt column with fixed one value

ON a similar way (Re: Have a quite typical issue regarding building logic in same column ) I want to calculate the average time

Average time =Avg  (Time stamp for ID 201- (Time stamp for ID 301 or Transit time for ID 199)).There can be more than one entry for Code for particular ID and latest updates time to be used for calculation

The time would be in days :Sample data

 ID status reason code Time bcda X2 P1 199 x bcda AF NS 102 y bcda O1 NS 201 z bcda X6 NS 206 a bcda AA NS bcda AC NS bcda AF NS 102 mn bcda OD NS 302 tx bcda P1 NS 202 cv bcda P1 NS 202 bh bcda X4 FN 201 nb bcda D1 NS 301 mn bcda CR R4 983 tx jhkg X2 P1 199 cv jhkg AF NS 102 bh jhkg O1 NS 201 nb jhkg AF NS 102 mn jhkg X6 NS 206 tx jhkg AA NS 102 cv jhkg AC NS bh jhkg AF NS 102 nb jhkg OD NS 302 mn jhkg P1 NS 102 tx jhkg X4 FN 201 cv jhkg D1 NS 301 bh jhkg CR R4 983 nb ABCD a NS 201 mn ABCD X2 P1 199 tx ABCD AF NS 102 cv ABCD X6 NS 206 bh ABCD AA NS ABCD AC NS ABCD AF NS 202 tx ABCD OD NS 302 cv ABCD P1 NS 302 bh ABCD AB BG ABCD P1 NS 202 mn ABCD X4 FN 201 tx ABCD D1 NS 301 cv ABCD CR R4 983 bh
• ###### 1. Re: Taking average of two values in differnt column with fixed one value

I don't understand what you want to accomplish from provided info.

What is your measure and what is your expected result for specific group?

Of course in any case, attaching packaged workbook is the best support for people in the community to answer your question easily.

Thanks,

Shin

• ###### 2. Re: Taking average of two values in differnt column with fixed one value

Hi Shin,

Apology for not being specific in my requirement!

So here I go- Lets have Corresponding to 102 Code timestamp as "Incoming Time" and for 301 Code as "Outgoing Time".I want to calculate the average of the difference of these:

Transit time = (Outgoing time - (Incoming Time)

Average = ((Outgoing time - (Incoming Time)) / total unique IDs

One problem is that for particular ID there can be more than one Code-102 or Code-301 and I need to have latest timestamp one in my calculation.

Regards,

Sudhanshu Shukla

• ###### 3. Re: Taking average of two values in differnt column with fixed one value

Timestamp is in Date Time format

• ###### 4. Re: Taking average of two values in differnt column with fixed one value

Could you prepare the sample data by yourself?

Thanks,

Shin

• ###### 5. Re: Taking average of two values in differnt column with fixed one value

Yes Sure Shin! I will add the sample data and that would be more helpful.

Regards,

Sudhanshu Shukla

• ###### 6. Re: Taking average of two values in differnt column with fixed one value

Hi Shin,

I have created a sample data and sent across. Let me know if this is still not clear. Regarding the calculation, I have shared in my earlier comments

• ###### 7. Re: Taking average of two values in differnt column with fixed one value

Sudhanshu

I have no clue what do you need from this data set you provided..

Thanks,

Shin

• ###### 8. Re: Taking average of two values in differnt column with fixed one value

Sorry Shin for not being very clear but I need average of transit time which will be calculated basis on the below formula:

(Incoming Code time - Outgoing Code time) / Total unique ID

• ###### 9. Re: Taking average of two values in differnt column with fixed one value

Hi All,

Can anybody help in this ? Ray Givler As you helped me last time ! This is basis on the last data but a bit of extended version?

Regards,

Sudhanshu Shukla