Median formula according to selected dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1308572%22%20slang%3D%22en-US%22%3EMedian%20formula%20according%20to%20selected%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1308572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screen%20Shot%202563-04-15%20at%2015.37.55.png%22%20style%3D%22width%3A%20360px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184454i3D2301F092E58126%2Fimage-dimensions%2F360x335%3Fv%3D1.0%22%20width%3D%22360%22%20height%3D%22335%22%20title%3D%22Screen%20Shot%202563-04-15%20at%2015.37.55.png%22%20alt%3D%22Screen%20Shot%202563-04-15%20at%2015.37.55.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhat%20formula%20do%20I%20have%20to%20put%20into%20F2%20(and%20F7%2C%20F12%2C%20F17%20respectively)%20in%20order%20to%20find...%3C%2FP%3E%3CP%3E-%20Median%20of%20the%20First%20Reply%20(excluding%20'null'%20results)%3C%2FP%3E%3CP%3E-%20The%20data%20F2%20refers%20to%20is%20in%20accordance%20with%20the%20date%20range%20from%20B2%3AB3.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20source%20where%20the%20formula%20could%20refer%20to%20the%20data.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screen%20Shot%202563-04-15%20at%2015.32.13.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184456i2C0645592E4E3A5B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screen%20Shot%202563-04-15%20at%2015.32.13.png%22%20alt%3D%22Screen%20Shot%202563-04-15%20at%2015.32.13.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1308572%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1309760%22%20slang%3D%22en-US%22%3ERe%3A%20Median%20formula%20according%20to%20selected%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1309760%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20mean%20by%20%22median%22%20that%20you%20want%20to%20calculate%20the%20date%20in%20the%20middle%20between%20the%20two%20dates%20in%20B2%20and%20B3%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20so%2C%20the%20formula%20to%20put%20in%20F2%20is%20%3CSTRONG%3E%3DB2%2B((B3-B2)%2F2)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20not%2C%20please%20come%20back%20and%20clarify.%20ideally%20by%20posting%20your%20actual%20spreadsheet%2C%20not%20just%20an%20image%20of%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1309937%22%20slang%3D%22en-US%22%3ERe%3A%20Median%20formula%20according%20to%20selected%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1309937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20sample%20in%20attached%20file%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%20320px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184551i52D1B4CC69B6E076%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%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMEDIAN(IF((%24B%242%3A%24B%2422%26gt%3B%3D%24E%242)*(%24B%242%3A%24B%2422%26lt%3B%3D%24F%242)%2C%24C%242%3A%24C%2422%2C%22null%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIt%20is%20assumed%20in%20every%20cell%20of%20the%20range%20you%20have%20text%20(%22null%22)%20or%20number.%20Function%20ignores%20all%20texts.%20It%20is%20assumed%20as%20well%20you%20have%20correct%20dates%20everywhere%2C%20not%20texts%20which%20represent%20the%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1311945%22%20slang%3D%22en-US%22%3ERe%3A%20Median%20formula%20according%20to%20selected%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311945%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20Sergei.%20I%20tried%20the%20formula%20you%20suggested%2C%20but%20this%20warning%20came%20up.%20What%20should%20I%20do%3F%3C%2FP%3E%3CP%3EThis%20data%20is%20transferred%20over%20to%20Google%20Sheets%20as%20I%20need%20to%20display%20it%20there.%20Is%20that%20why%20there's%20a%20null%20error%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202563-04-16%20at%2015.01.07.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184733i7A9C69E018A68473%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Screen%20Shot%202563-04-16%20at%2015.01.07.png%22%20alt%3D%22Screen%20Shot%202563-04-16%20at%2015.01.07.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312134%22%20slang%3D%22en-US%22%3ERe%3A%20Median%20formula%20according%20to%20selected%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584048%22%20target%3D%22_blank%22%3E%40little2fern%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20Excel%3A%3C%2FP%3E%0A%3CP%3E1)%20That%20is%20an%20array%20formula%20which%20shall%20be%20entered%20by%20Ctrl%2BShift%2BEnter%20if%20only%20you%20are%20not%20on%20Excel%20with%20Dynamic%20Arrays%3C%2FP%3E%0A%3CP%3E2)%20MEDIAN()%20ignores%20all%20texts%20in%20the%20range%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20no%20idea%20how%20it%20works%20in%20Google%20Sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Screen Shot 2563-04-15 at 15.37.55.png

What formula do I have to put into F2 (and F7, F12, F17 respectively) in order to find...

- Median of the First Reply (excluding 'null' results)

- The data F2 refers to is in accordance with the date range from B2:B3.

 

 

 

 

 

 

 

Here is the source where the formula could refer to the data.

Screen Shot 2563-04-15 at 15.32.13.png

4 Replies
Highlighted

@little2fern 

 

Do you mean by "median" that you want to calculate the date in the middle between the two dates in B2 and B3?

 

If so, the formula to put in F2 is =B2+((B3-B2)/2)

 

If not, please come back and clarify. ideally by posting your actual spreadsheet, not just an image of it.

Highlighted

@little2fern 

For the sample in attached file

image.png

formula could be

=MEDIAN(IF(($B$2:$B$22>=$E$2)*($B$2:$B$22<=$F$2),$C$2:$C$22,"null"))

It is assumed in every cell of the range you have text ("null") or number. Function ignores all texts. It is assumed as well you have correct dates everywhere, not texts which represent the dates.

Highlighted

@Sergei Baklan 

 

Hello, Sergei. I tried the formula you suggested, but this warning came up. What should I do?

This data is transferred over to Google Sheets as I need to display it there. Is that why there's a null error?Screen Shot 2563-04-16 at 15.01.07.png

Highlighted

@little2fern 

For Excel:

1) That is an array formula which shall be entered by Ctrl+Shift+Enter if only you are not on Excel with Dynamic Arrays

2) MEDIAN() ignores all texts in the range

 

Have no idea how it works in Google Sheets.