Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1493716%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493716%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20guys%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20on%20what%20formula%20to%20use%20to%20create%20values%20in%20the%20columns%20shown%20in%20the%20excel%20file%20attached.%20I%20have%20my%20heart%20rate%20data%20in%20which%20the%20'date%20and%20time'%20values%20vary.%20I%20want%20to%20create%20a%20more%20structure%20data%20set%20in%20which%20the%20'date%20and%20time'%20values%20are%20within%20time-periods.%20For%20example%2C%20any%20heart%20rate%20extracted%20between%20date%20XX%2FXX%2F2020%2C%201%3A00pm%20-%202%3A00pm%20(then%20the%20min%2C%20max%20and%20median%20to%20be%20shown).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFYI%20it's%20to%20help%20make%20visualisations%20on%20tableau.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20greatly%20appreciated%2C%20thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1493716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1493819%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711960%22%20target%3D%22_blank%22%3E%40dillanpatel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20did%20that%20with%20Power%20Query%20and%20when%20started%20to%20reply%20recognized%20you%20are%20on%20Mac.%20Thus%20most%20probably%20won't%20work%2C%20however%20file%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1493873%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1493873%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthanks%20for%20the%20help%20-%20as%20I%20can't%20accomplish%20this%20on%20mac%2C%20would%20you%20mind%20conducting%20the%20exact%20same%20query%20fort%20his%20document%20too%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20appreciate%20the%20help%20Sergei.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494095%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711960%22%20target%3D%22_blank%22%3E%40dillanpatel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20added%20the%20table%20generated%20by%20Power%20Query.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20another%20sheet%20is%20the%20same%20but%20generated%20without%20data%20model%20and%20Power%20Query.%20You%20may%20add%205%20columns%20to%20the%20source%20table%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20510px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201699i4BD2E83F3271A244%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFormulas%20are%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EStart1%3A%0A%3DINT(%5B%40Start%5D)%2BTIME(FLOOR(HOUR(%5B%40Start%5D)%2C1)%2C0%2C0)%0A%0AFinish1%3A%0A%3D%5B%40Strart1%5D%2B1%2F24%0A%0AMin%3A%0A%3DAGGREGATE(17%2C6%2C1%2F(%5BStrart1%5D%3D%5B%40Strart1%5D)*%5BHeart%20Rate%20(count%2Fmin)%5D%2C0)%0A%0AMax%3A%0A%3DAGGREGATE(17%2C6%2C1%2F(%5BStrart1%5D%3D%5B%40Strart1%5D)*%5BHeart%20Rate%20(count%2Fmin)%5D%2C4)%0A%0AMedian%3A%0A%3DAGGREGATE(17%2C6%2C1%2F(%5BStrart1%5D%3D%5B%40Strart1%5D)*%5BHeart%20Rate%20(count%2Fmin)%5D%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20you%20may%20pivot%20that%20table%20on%20Start1.%20Or%2C%20as%20variant%2C%20remove%20duplicates.%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hey guys, 

 

I'm not sure on what formula to use to create values in the columns shown in the excel file attached. I have my heart rate data in which the 'date and time' values vary. I want to create a more structure data set in which the 'date and time' values are within time-periods. For example, any heart rate extracted between date XX/XX/2020, 1:00pm - 2:00pm (then the min, max and median to be shown). 

 

FYI it's to help make visualisations on tableau.

 

Any help will be greatly appreciated, thank you. 

3 Replies
Highlighted

@dillanpatel 

I did that with Power Query and when started to reply recognized you are on Mac. Thus most probably won't work, however file is attached.

Highlighted

@Sergei Baklan thanks for the help - as I can't accomplish this on mac, would you mind conducting the exact same query fort his document too? 

 

Really appreciate the help Sergei. 

Highlighted

@dillanpatel 

I added the table generated by Power Query.

 

In another sheet is the same but generated without data model and Power Query. You may add 5 columns to the source table

image.png

Formulas are

Start1:
=INT([@Start])+TIME(FLOOR(HOUR([@Start]),1),0,0)

Finish1:
=[@Strart1]+1/24

Min:
=AGGREGATE(17,6,1/([Strart1]=[@Strart1])*[Heart Rate (count/min)],0)

Max:
=AGGREGATE(17,6,1/([Strart1]=[@Strart1])*[Heart Rate (count/min)],4)

Median:
=AGGREGATE(17,6,1/([Strart1]=[@Strart1])*[Heart Rate (count/min)],2)

 

After that you may pivot that table on Start1. Or, as variant, remove duplicates.

Please check in attached file.