Ratio of Fields from Different Sheets in Excel Pivot

[WWGD]

Hi All,
I want to compute the ratio of two fields A,B from different sheets ( same workbook)
, using Pivot ( tho "straight" Excel will do*). I tried using the Wizard, but , in the end, could
not find a way of doing it:

Any ideas, please?
*I scewed big when trying this.

 

[StoneTemplePython]

Can you explain a bit more? I could possibly find a clean way to do this, though I already have a few hacks in mind.

 

[WWGD]

Thanks. I have two fields A,B given, both in the same workbook, although in different sheets, and I need to compute the field C:=A/B within Excel. It is a bit more problematic in that I do need to find the values of A,B for the same instance, say $i$ in both tables, so that I need something like a join, to make sure I compute $A_i/B_i$ instead of $A_i/B_j ; j \neq i$. Because of complicated reasons, I cannot do it within SQL. I tried to use Microsoft Query, but the connection was poor ( strangely so, given I could export data to/from SQL Server from Excel, and , of course, SQL server was running.); so I cannot " pullback" query results from Microsoft Query back to Excel.

 

[StoneTemplePython]

I think this is going to be hard to explain over text as excel cells aren't quite standardized enough... (if you were using pandas or numpy in python things are almost taylor made for forum correspondence-- it really would be as simple as dropping in a couple lines of code... )

Does the below sketch work?
- - - -
you have two pivot tables. Linking to pivots is the same as as with regular cells excel if you 'click' the pivot cell it won't have a regular cell reference. You want underlying cell references, so don't click them. So on a new worksheet tab, you want to create a formula that references the two pivot tables-- here in your references you type in cell formula (in your picture the cells would be something like "$Sheet4!A$4: $Sheet4!?$?") for both of your arrays. you grab the values you want by vlookups, and then divide things as needed.

- - - -
I felt uncomfortable while writing that as it seems we have the wrong tool for the job on two levels here (i.e. explaining this by text with clunky excel interface -- maybe a youtube video would be ok though) and I get less and less happy with spreadsheets as the right tool for anything, but I digress.

Some extra care is needed in dealing with pivot tables, on a few different levels. Not sure how much that helps.

 

[WWGD]

I think this is going to be hard to explain over text as excel cells aren't quite standardized enough... (if you were using pandas or numpy in python things are almost taylor made for forum correspondence-- it really would be as simple as dropping in a couple lines of code... )

Does the below sketch work?
- - - -
you have two pivot tables. Linking to pivots is the same as as with regular cells excel if you 'click' the pivot cell it won't have a regular cell reference. You want underlying cell references, so don't click them. So on a new worksheet tab, you want to create a formula that references the two pivot tables-- here in your references you type in cell formula (in your picture the cells would be something like "$Sheet4!A$4: $Sheet4!?$?") for both of your arrays. you grab the values you want by vlookups, and then divide things as needed.

- - - -
I felt uncomfortable while writing that as it seems we have the wrong tool for the job on two levels here (i.e. explaining this by text with clunky excel interface -- maybe a youtube video would be ok though) and I get less and less happy with spreadsheets as the right tool for anything, but I digress.

Some extra care is needed in dealing with pivot tables, on a few different levels. Not sure how much that helps.

Thanks, and sorry I forgot to include this: What makes the ratio A/B more complicated is that B is an aggregate, making a SQL query clunkier ( needing to do group by' s )