Median If across workbook with several sheets

[VCarr]

Hi,
I have a workbook with several sheets. In each sheet, I have three columns: Column A = name; Columns B and C are dates.

I need a formula that says if column A (in any sheet) = TEXT, then calculate the median number of days between Columns B and C (again taking each sheet into account). Is this possible?

 

[Domenic]

If, for example, you have three worksheets, and assuming that Column B contains the start date, and Column C contains the end date, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=MEDIAN(IF(Sheet1!A2:A100="Text",Sheet1!C2:C100-Sheet1!B2:B100),IF(Sheet2!A2:A100="Text",Sheet2!C2:C100-Sheet2!B2:B100),IF(Sheet3!A2:A100="Text",Sheet3!C2:C100-Sheet3!B2:B100))

Adjust the ranges, accordingly.

 

[VCarr]

Thanks - I'll try it out now

 

[VCarr]

It works - thank you so much. I will now look very clever at work

 

[gerrardj]

Hi, I have used the same principle as above, however the value does not seem to be correct. I have two sheets, each containing panel data. Sheet 1 contains years 2000-2009 for each firm ID, Sheet 2 contains the years 2010-2012 for each firm ID. Each firm ID is assigned an industry code (which is of course constant over time). Column F is the column I want to get the median for. I made a new sheet where each industry code is listed. Next to the industry code, I entered the following formula:

---

=MEDIAN(IF(Blatt1!$C$2:$C$920869=A2;Blatt1!$F$2:$F$920869);IF(Blatt1!$B$2:$B$920869=$B$1;Blatt1!$F$2:$F$920869))

---

Column C of each sheet contains the industry code, column B the year. So the if-conditions in this array-formula are: year is as in $B$1, industry as in A2. Entering this as an array-formula, I get the value 0.757741 . To check whether the result is correct, I filtered the original dataset, only showing the year 2000 and the desired industry code. Taking the median of the values in column F then, I get 0.744019. How can this error occur?

 

[AlphaFrog]

How did you calculate the MEDIAN of the filtered values? If you just used the MEDIAN function on the filtered results, the function doesn't respect the filter. It will include the hidden filtered values.

 

[gerrardj]

How did you calculate the MEDIAN of the filtered values? If you just used the MEDIAN function on the filtered results, the function doesn't respect the filter. It will include the hidden filtered values.

I was aware of that, so after filtering, I copy-pasted the values out of the filtered panel into a new sheet and then ran the median formula...

 

[Domenic]

Hi, I have used the same principle as above, however the value does not seem to be correct. I have two sheets, each containing panel data. Sheet 1 contains years 2000-2009 for each firm ID, Sheet 2 contains the years 2010-2012 for each firm ID. Each firm ID is assigned an industry code (which is of course constant over time). Column F is the column I want to get the median for. I made a new sheet where each industry code is listed. Next to the industry code, I entered the following formula:

---

=MEDIAN(IF(Blatt1!$C$2:$C$920869=A2;Blatt1!$F$2:$F$920869);IF(Blatt1!$B$2:$B$920869=$B$1;Blatt1!$F$2:$F$920869))

---

Column C of each sheet contains the industry code, column B the year. So the if-conditions in this array-formula are: year is as in $B$1, industry as in A2. Entering this as an array-formula, I get the value 0.757741 . To check whether the result is correct, I filtered the original dataset, only showing the year 2000 and the desired industry code. Taking the median of the values in column F then, I get 0.744019. How can this error occur?

For the one sheet, try...

=MEDIAN(IF(Blatt1!$C$2:$C$920869=A2;IF(Blatt1!$B$2:$B$920869=$B$1;Blatt1!$F$2:$F$920869)))

For both sheets, try...

=MEDIAN(IF(Blatt1!$C$2:$C$920869=A2;IF(Blatt1!$B$2:$B$920869=$B$1;Blatt1!$F$2:$F$920869)),IF(Blatt2!$C$2:$C$920869=A2;IF(Blatt2!$B$2:$B$920869=$B$1;Blatt2!$F$2:$F$920869)))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Christopher lee]

sry, may i ask u all know hw to calculate median via VBA Coding in macro? i need a coding to calculate median on my excel sheet and then put in a new column.....

 

[AlphaFrog]

Try something like this...

Md = Application.WorksheetFunction.Median(Range("A1:A10"))