List Bldg # according to sum of hours and bldg rank....

[tim m]

Greetings,

I have the following scenario that I'm trying to wrap my head around.

(col A) I have a column with a unique Building identifying numbers.
(col B) I have a column with a building ranking # (ranks how important the
building is, this not necessarily unique, there will be several buildings
with the same rank #)
(col C) Number of maintenance hours for each building
(Cell D2) a building maintenance hours number calculated from other data.

In column E I need to add up the building maintenance hours (smallest to
largest) for the buildings up to the number in D2 and then list which
buildings #'s are invloved. But starting with the building with the lowest
ranking (3 being lowest, 1 being highest)

Sample data:

Bldg # Bldg rank Bldg maint hrs hours from calculation
1 1 15 100
2 1 30
3 2 35
4 2 50
5 3 20
6 3 10

So from the example above I want to add up bldg maint hours up to 100 and
list the bldg #'s with preference being given to the lower rankings 1st. so
for the example above it should pick bldg# 6, 5, 4, and then 1 (6,5,4 =80+1
=15) total 95

 

[Bernie Deitrick]

Tim,

In cell E2, enter the formula

=IF(C2<=($D$2-SUM($E3:E$XXX)),C2,0)

Replace the XXX with a number higher than the highest row in your table, then copy this down to
match your data table.

HTH,
Bernie
MS Excel MVP

 

[tim m]

Thanks Bernie, that shows the hours of which buildings that add up to less
than 100 but I need to get the bldg #'s from column A. (I was able to
quickly make another formula =IF(E2>0,A2,"") that will give me a building
number from your formula) but perhaps your formula can be made to display the
building #'s rather than the hours of those buildings?

 

[Bernie Deitrick]

Tim,

In cell E2, use the formula

=IF(C2<=($D$2-SUMIF(E3:E$XXX,"<>",C3:$C$XXX)),A2,"")

Or perhaps, since the building name is already in column A:

=IF(C2<=($D$2-SUMIF(E3:E$XXX,"<>",C3:$C$XXX)),"Maintained","")

HTH,
Bernie
MS Excel MVP