Median in Pivot Table in Excel 2010?

Question

I'm able to work this statement to a point but I'd like to add another outcome. How to get the statement to return a value from the same row (headed 'first_name') in the CSV if the boolean statement returns true?

def customer_check(user_pin)

  x = false

  CSV.read('customers.csv', headers: true).any? do |row|
    x = true if row['pin'] == user_pin and row['work_here'] == "YES"
    yellow = row['first_name']

  if x == true then
    puts "Welcome back #{yellow}."
    sleep(1.5)
  else
    puts "login failed. Please try again in 3 seconds..."
    sleep(3.0)
    login_start
  end
    navigation_menu
end

Solution 1

For simple examples you can use array formulas instead of a PivotTable.

If you have the source data in rows 10:1000, category designations of the source data in column A, the source data values in column C, and the category being considered in G3, the following array formula will find the median:

{=MEDIAN(IF($A$10:$A$1000=G3,$C$10:$C$1000))}

Commit the entry with Ctrl+Shift+Enter, and copy down for the categories in G4, G5 etc

Solution 2

The quickest and simplest way to get a median in your pivot tables is to import your Excel file into Google Sheets. There you can create a pivot table and use a median.

Solution 3

Unfortunately, there is nothing built in to excel's pivot table function that will do this. You could try this add-on though. It claims to be able to do it, but I've never used it.

You could do the median work with the data and then include it in pivot table data, but at that point.. you know.. what's the point of the table..

Solution 4

You can actually use the iserror function to exclude the total rows from medians. For example, if the Total row labels are in row A and the data you want the median of is in row I:

=MEDIAN(IF(ISERROR(FIND("Total",$A$5:$A$65535)),I5:I35535))

View more solutions

Comments

[dav]

IF memory serves, Excel's aggregate functions are closely related to SQL's aggregate functions-and you won't find a Median function there either. A SQL work-around may give some insight into creating an Excel Pivot table version.

[dav]

You may be able to leverage Powerpivot to do what you need, see this article: javierguillen.wordpress.com/2011/09/13/….

[Jagdeep Singh]

You can refactor x = true if row['pin'] == user_pin && row['work_here'] == "YES" to x = row['pin'] == user_pin && row['work_here'] == "YES" and second, if x == true then to if x only.

[Jagdeep Singh]

Isn't there an extra end keyword in above code? Can you please fix the indentation? It will be more clear then.

[Paul Thompson]

Sorry all I have just updated the code. Hope it makes more sense. @Jagdeep the changes you proposed didn't seem to work out but maybe because the code was incorrect.

[jhamu]

Looks like there is typo for value where instead of $C$10, $C$100 is there.

[Paul Thompson]

apologies Mr Kennedy I updated the original questions. Hope it makes more sense.

[coolcheese]

Another thing, I wouldn't really use and or or for logical operations in conditionals - Difference between “and” and && in Ruby?

[MmmHmm]

@PaulThompson, no need to apologize! Don't forget to select an answer and vote for useful comments/answers :)

[Dan]

I had to vote this up because it's by far the easiest way to get the results. Doesn't help in excel, but the same data can be processed there in a pinch.

[adolf garlic]

No good if you have sensitive data

[fixer1234]

External links can break or be unavailable, in which case your answer would not add anything useful useful. Please include the essential information within your answer and use the link for attribution and further reading. Thanks.