# Tricky Data Range Question

**Elizabeth Knight**Oct 5, 2011 3:57 PM

Hi Everyone,

I am working on a pivot table that will calculate a value, compare that value to a range, and based on where the calculated value falls in the range, display a final value on the report. I'm going to try to explain this using an example:

I have 4 ranges, one for each 'Type':

Type A Range: | ||

MinRange | MaxRange | Limit |

0 | 2500 | 1 |

2501 | 5000 | 2 |

5001 | 7500 | 3 |

7501 | 10000 | 4 |

Type B Range: | ||

MinRange | MaxRange | Limit |

0 | 5000 | 1 |

5001 | 10000 | 2 |

Type C Range | ||

MinRange | MaxRange | Limit |

0 | 3000 | 1 |

3001 | 6000 | 2 |

6001 | 9000 | 3 |

9001 | 12000 | 4 |

Combined Range | ||

MinRange | MaxRange | Limit |

0 | 2000 | 1 |

2001 | 4000 | 2 |

4001 | 6000 | 3 |

6001 | 8000 | 4 |

8001 | 10000 | 5 |

10001 | 12000 | 6 |

The final report should look like this:

Center | Type | Month1 | Month2 | Total1 | Total2 | Limit | Remaining |

1 | A | 0 | 0 | 0 | 5000 | 2 | 2 |

1 | B | 0 | 0 | 0 | 5000 | 1 | 1 |

1 | C | 1 | 0 | 1 | 5000 | 2 | 1 |

1 | Total | 1 | 0 | 1 | 5000 | 3 | 2 |

2 | A | 0 | 1 | 1 | 3500 | 2 | 1 |

2 | B | 0 | 1 | 1 | 3500 | 1 | 0 |

2 | C | 0 | 0 | 0 | 3500 | 2 | 2 |

2 | Total | 0 | 2 | 2 | 3500 | 2 | 0 |

3 | A | 0 | 0 | 0 | 9000 | 4 | 4 |

3 | B | 0 | 0 | 0 | 9000 | 2 | 2 |

3 | C | 2 | 0 | 2 | 9000 | 3 | 1 |

3 | Total | 2 | 0 | 2 | 9000 | 5 | 3 |

Where:

- 'Total1' is a total of the values in 'Month1' and 'Month2' for each 'Type'

- 'Total2' is a total of production, completely separate from all previous information and is calculated by summing the aggregate of production by Center only, using NoDistinct

- 'Limit' is to be determined by locating where 'Total2' falls in the ranges above for each type, and using the combined range for the total line.

- 'Remaining' is 'Limit' - 'Total1'

Right now, I have a lookup table for the ranges, keyed off of 'Type' and with a Sequence Number to identify each line. I have tried several things, and I believe I have gotten the closest with the following:

**If(Count({$<"$vTotalProduction" = {">$(=VMSMinRange) <$(=VMSMaxRange)"}>} Distinct VMSSeqNo) = 1, VMSLimit)**

In the above set analysis, vTotalProduction = the same equation used to calculated 'Total2'. However, because I haven't figured out how to isolate the 'Type' in this set analysis, I have to make a selection in Type to narrow it down, and this still counts all of the range values for that type rather than isolating the one record where vTotalProduction is within the range.

Has anyone ever encountered something like this? Any help would be greatly appreciated!