1. Calculate a % of total within certain groupings. So for instance, my table may look something like this:

Group 1 | Group 2 | Group 3 | Value | |

Red | Light | A | 100 | |

Red | Medium | B | -50 | |

Yellow | Dark | C | 400 | |

Orange | Medium | B | 500 | |

Red | Light | C | 200 |

<tbody>

</tbody>

So what I need this formula to do is calculate the % of total based on the pairing of Group 2 and 3 but within Group 1. For instance,

Red > Light > A should be 100 / (100 + (-50) + 200) = 40%

Red > Medium > B should be - 50 / (100 + (-50) + 200) = -20%

There is one additional step and that is that I need to calculate based on absolute values. So my examples above would turn into:

Red > Light > A should be 100 / (100 + 50 + 200) = 29%

Red > Medium > B should be -50 / (100 + 50 + 200) = 14%

So that is formula # 1 I need.

The second one that I need is to calculate the average but also be able to calculate a trimmed average. It needs to be based on the combination of all 3 groupings (my source data will have many more rows). But essentially I would need it to calculate the average of all rows where 1 = Red, 2 = Medium, and 3 = B ... and to be able to average only if the values are within a set range. Otherwise, just ignore them.

I could do all of this in an Excel table but is this the sort of thing Powerpivot would help with?