|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
"tim m" <timm[ at ]discussions.microsoft.com> wrote in message
news:DBFECFE7-07FF-4D90-9279-D00ADBC4D1A2[ at ]microsoft.com...
[Quoted Text] > 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
>
>
>
>
|
|
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" wrote:
[Quoted Text] > 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" <timm[ at ]discussions.microsoft.com> wrote in message
> news:DBFECFE7-07FF-4D90-9279-D00ADBC4D1A2[ at ]microsoft.com...
> > 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, 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
"tim m" <timm[ at ]discussions.microsoft.com> wrote in message
news:509AD17F-8073-473E-B388-C1B33B1C6438[ at ]microsoft.com...
[Quoted Text] > 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" wrote:
>
>> 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" <timm[ at ]discussions.microsoft.com> wrote in message
>> news:DBFECFE7-07FF-4D90-9279-D00ADBC4D1A2[ at ]microsoft.com...
>> > 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
>> >
>> >
>> >
>> >
>>
>>
>>
|
|
|