•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Pivot Calculated Field issue
Our take
Navigating Excel's Pivot Tables can be challenging, especially when trying to insert formulas from a manual table. In your case, you’re aiming to analyze 200 transactions to uncover the root cause of a sudden decline in approval rates. By creating a pivot table to check approval rates based on Card type, Country, and Merchant ID, you can gain valuable insights. With a sample formula already in hand for the Card type, mastering its application in your pivot table will pave the way for further analysis.
Hi guys, Im still learning excel and im having a hard time inserting a formula in a pivot table that initially created in a manual table.
I wanted to to utilize the pivot function of opening a new sheet with the filtered values + the formulas that I want to show.
Here's a link to the sample data set that i'm using. Here's the breakdown:
- have 200 transactions over a 3 day period (no dates for this one sadly) with a sudden decline in approval rate
- divided it into 2 groups (first half, last half)
- Wanted to make a pivot table checking the approval rates of each of the following: Card type, Country, MID (merchant ID).
- This is just to investigate the root cause.
I already have a sample formula for the Card type part but having trouble inserting it in a pivot table. It should be easy to apply to other pivot tables once I get this one done
=(COUNTIFS(Raw!H:H,"First 100",Raw!B:B,"Approved",Raw!D:D,"Amex")/COUNTIFS(Raw!H:H,"First 100",Raw!D:D,"Amex")
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table Show Details not working, any idea?Hi there, I am facing with a quite unique issue I think. There are some pivots in an Excel file that we use for internal reports for the AP team. The issue is that for the pivots there are several filters applied on, like Intercompany filter or "is the amount negative" etc. but there is the most important one is the Vendor filter, there are numerous vendors excluded, and I was like okay let me just recreate the pivot from the ground, but that would take a bit too much due to the vendor filter. The main problem is that the show details is not working, if I execute a double click on the pivot's total cells then it is loading for a 0.1 second and then nothing happens. If I try it after I close and reopen the file, then it loads for 2 seconds, with the botom right text saying "Reading Data" but then nothing, same results. It is a connection based pivot table, I tried to copy and paste into a new sheet the Pivot, didn't work, I tried to Save & Repair the file, it didn't work. Any idea? submitted by /u/Strange_Cell1142 [link] [comments]
- Formula to re-arrange data set to be able to create a pivot table.I am looking for a formula to re-arrange the data I have: https://preview.redd.it/vkdes6wyxqrg1.png?width=345&format=png&auto=webp&s=a30397f809384603a701af85ad807c46b9bdacf4 This is the format I want: https://preview.redd.it/43zatlm0yqrg1.png?width=450&format=png&auto=webp&s=902ea4ac22cb499f567009801ba6def7766b929e I need to do this the pay data of approx. 100 employees, each employee has a different set categories so the number of times the name needs to repeat will vary. I want to do this so I can create a pivot table, any suggestions are welcome. Using Excel365 Desktop - Beginner to intermediate level knowledge Thank you in advance. submitted by /u/Ok_Chicken_590 [link] [comments]
- pivot tables - exist something like filter by column?Good morning community. I don’t usually use pivot tables much, because I tend to prefer building my reports with filters and formulas, but my boss loves them. The problem is that many times he doesn’t even know exactly what he really wants. Here’s the issue. I have a table where data is divided into categories, for example: Column A – Primary Categories Column B – Secondary Categories Column C – Tertiary Categories From Column D onward… prices, with each column representing a different month. My boss wants a pivot table where he can filter by categories (I already have that done), but also be able to filter somehow by just one month, or several, or all of them, since he then wants to use that in a chart (this part is also already done). AND HE ONLY WANTS TO SEE THE CHART AND CONTROL THE DATA FROM THERE—in other words, he doesn’t want to have to go into the pivot table to edit it by adding or removing columns. So the question is… how can I (if it’s even possible directly from the pivot table) quickly change the columns I’m displaying with a button? That is, without having to manually edit the pivot table. So far, the solution I found was to create a “column” using formulas where I bring in the data with “HLOOKUP” and change the filter with a dropdown list linked to a macro that refreshes the pivot table every time its value changes. But I’d like to know if pivot tables have a more direct way to solve this. Thank you very much. submitted by /u/Potential-Aside-1712 [link] [comments]
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#financial modeling with spreadsheets#AI formula generation techniques#Excel compatibility#rows.com#real-time data collaboration#formula generator#Excel alternatives#big data management in spreadsheets#machine learning in spreadsheet applications#conversational data analysis#intelligent data visualization#real-time collaboration#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions