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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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?
 
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
 
Back
Top