Excel 2007 : Trying to split number into ranges

**daveydet** · 11-02-2010, 01:06 PM

I work for a water utility inwhich we have tiered rates based on number of units used. I need to find a formula that will take the total number of unit and split it up based on different tiers. See example:

Total Units Tier 1 Tier 2 Tier 3
(1-14) (15-49) (50+)
________________________________________

78 = 14 35 29

I know there is a way to do this but have been unsuccessful in trying. Thanks in advance.

**scottylad2** · 11-02-2010, 01:19 PM

Originally Posted by daveydet

I work for a water utility inwhich we have tiered rates based on number of units used. I need to find a formula that will take the total number of unit and split it up based on different tiers. See example:

Total Units Tier 1 Tier 2 Tier 3
(1-14) (15-49) (50+)
________________________________________

78 = 14 35 29

I know there is a way to do this but have been unsuccessful in trying. Thanks in advance.

from your example, how would one know what Tier to distribute the 78 amongst? is there some reference to amounts within the data that are say less than or = to each tier?

**daveydet** · 11-02-2010, 01:54 PM

Yeah, it did not appear right in the example, so lets say the total number is 78 and the tiers are as follows:
1-14 units - tier 1
15-49 units - tier 2
50 and above - tier 3

So basically I need a formula for each tier that will look at total untis and distribute them according to the different tiers. so in this instance tier 1 would be 14, tier 2 would be 35 and tier 3 would be 29.