Dynamic formula based on pivot table

[Mechixx]

Hello there, i have kind of a perplexing question that I'm not sure which way to go about it. To set the stage:

i have data which has calculated mean time between failures for our shop at work, and my boss wants me to create something that provides a percentage of the good trends over the bad ones (we want the trend to go up from previous month to current month).
So i use Access to store all the data, then create a query in Access that filters out all the other months accept for the last 2, then i use a pivot table to congregate that data as so:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Machines[/TD]
[TD]Mar 01/19[/TD]
[TD]Apr 01/19[/TD]
[/TR]
[TR]
[TD]2294[/TD]
[TD]2193[/TD]
[TD]3309[/TD]
[/TR]
[TR]
[TD]2355[/TD]
[TD]3304[/TD]
[TD]2942[/TD]
[/TR]
[TR]
[TD]2357[/TD]
[TD]8844[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


so this is perfect for calculating the trend, the number under march we want smaller than the number under april, and where theres an instance of either column not being completed, i want to ignore.

Now here in lies my question: i either want to create a calculated column in the pivot table, a dynamic range outside the pivot table that adjusts as the pivot table does, or do it in Access somehow, where if column (march) < (april) then put an "o" and if (march) > (april) then put an "x" or some kind of TRUE/FALSE condition so that i can then calculate the percentage of o vs x or true/false.

right now to the right of this pivot table i just have the formula:
=IF(OR(D8="",E8=""),"",IF(D8< E8,"o","x"))<e8,"o","x"))
<e8,"o","x"))<e8,"o","x"))<e8,"o","x"))<e8,"o","x")) <e8,"o","x"))<e8,"o","x"))[="" code]

and ive just dragged this formula down for 100 rows to encompass the data of the pivot. this works just fine, but i want something dynamic that changes based on the pivot table so incase this pivot table does get bigger than 100 that ittl still calculate what i need. then i just have a few COUNTIF formulas that count the number of "o" vs "x" and get my percentage.


Does anyone have any ideas on how to make this dynamic? create some type of dynamic range? should i try and do this in Access somehow?
Any help on this would be appreciated, thanks!</e8,"o","x"))<e8,"o","x"))<e8,"o","x"))<e8,"o","x"))></e8,"o","x"))

 

[jondavis1987]

I've never messed with access but couldn't you just make a table where all of this is stored and have a column that just has that formula so every time information gets entered into the table it generates automatically. Then you could add that range in the pivot table.

 

[Mechixx]

Ya since im using Access to store all this data, i was able to figure out how to unpivot this data in access and create a table that i can then figure out if its a good trend or not, then bring it back into excel to show.

Thanks though @jondavis1987