Applying a formula for daily percentages

[Fido]

I have a worksheet that takes inventory of units that i have available to
rent. What i would like to achieve is to have excel give me a daily
percentage of my different units filled and my total units filled without
having to apply a formula per day.

For example, i have different classes of units (1 bed, 2 bed, 3 bed, etc)and
id like to know how to tell excel to look at just today's filled units vs
total units (withing classification) to give me a percent. Also, i would like
to know my total occupancy rate for all units. Hope fully this makes sense to
someone, but if not just post and ill try again.

 

[smartin]

I have a worksheet that takes inventory of units that i have available to
rent. What i would like to achieve is to have excel give me a daily
percentage of my different units filled and my total units filled without
having to apply a formula per day.

For example, i have different classes of units (1 bed, 2 bed, 3 bed, etc)and
id like to know how to tell excel to look at just today's filled units vs
total units (withing classification) to give me a percent. Also, i would like
to know my total occupancy rate for all units. Hope fully this makes sense to
someone, but if not just post and ill try again.

Can you show us how your data is organized? Just post a few
representative rows and columns.

 

[Fido]

Can you show us how your data is organized? Just post a few
representative rows and columns.

Can you show us how your data is organized? Just post a few
representative rows and columns.

Sure, or at least ill try:

Columns b-g (row 1) are designated with unit class (1 bed, 2 bed, 3bed,
etc.) and each day of the month has its own row, starting at row3. Row 2 has
the total number of units for the classification (15 1-beds, 30 2-beds, 45
3-beds, etc.). Column A has the day of the month. For each day of the month,
i want to fill in the used number of units for that day. After the last day
of the month i would like a Daily Percentage row for columns b-g. I want the
daily percentage row to look at ONLY the current days worth of used units to
available units without having to change the formula every day. I do not want
a dedicated percentage column for each classification of unit, it needs to be
a row. There in lies the challenge.

Column H will be used as a Totals column. For example, H3 is used as a total
number of units for all classifications. Anything after H3 to the Daily
percentage column is used as a total occupancy rate.


Hopefully this makes a little more sense as im not sure how else to explain
it rather than post a link to the document in some fashion.

 

[smartin]

Sure, or at least ill try:

Columns b-g (row 1) are designated with unit class (1 bed, 2 bed, 3bed,
etc.) and each day of the month has its own row, starting at row3. Row 2 has
the total number of units for the classification (15 1-beds, 30 2-beds, 45
3-beds, etc.). Column A has the day of the month. For each day of the month,
i want to fill in the used number of units for that day. After the last day
of the month i would like a Daily Percentage row for columns b-g. I want the
daily percentage row to look at ONLY the current days worth of used units to
available units without having to change the formula every day. I do not want
a dedicated percentage column for each classification of unit, it needs to be
a row. There in lies the challenge.

Column H will be used as a Totals column. For example, H3 is used as a total
number of units for all classifications. Anything after H3 to the Daily
percentage column is used as a total occupancy rate.


Hopefully this makes a little more sense as im not sure how else to explain
it rather than post a link to the document in some fashion.

Sorry about the delayed reply.

I think I follow you. The dates in A will run from row 3 to (at most)
row 33 for a single month. Row 35 is convenient place to determine the
current utilization of each unit class.

E.g., in B35 and fill right:
=INDEX(B$3:B$33,COUNT(B$3:B$33),)/B$2

In column H, H2 (you said H3?) is the total availability, simply
=SUM(B22)

On each day row starting in H3 and filling down, the daily total
utilization is
=SUM(B33)/SUM($B$2:$D$2)

As a check, you can put the following in H35. It should match the most
recent total utilization in column H because it is the weighted average
of each unit class's utilization:
=SUMPRODUCT($B$35:$D$35,$B$2:$D$2)/SUM($B$2:$D$2)

 

[Fido]

I greatly appreciate you trying to help me but i guess im just not
understanding this. I uploaded the document in question to google docs. Would
you mind taking a look at it and helping me out further?

http://spreadsheets.google.com/ccc?key=0AuRnIcfpg1eCdFFYTlowNFFwdkc5a003VXBlN1NDOXc&hl=en

 

[smartin]

I greatly appreciate you trying to help me but i guess im just not
understanding this. I uploaded the document in question to google docs. Would
you mind taking a look at it and helping me out further?

http://spreadsheets.google.com/ccc?key=0AuRnIcfpg1eCdFFYTlowNFFwdkc5a003VXBlN1NDOXc&hl=en

Looking at your google doc, the solution is just like I gave before,
only the references have changed. I have adapted the formulas to fit
your posted example.

The most recent percent by unit is in B45 and fill right:
=INDEX(B$13:B$43,COUNT(B$13:B$43),)/B$10

The total total utilization per day is in H13 and fill down:
=SUM(B13:G13)/SUM($B$10:$G$10)

As a check, you can put the following in H45. It should match the most
recent daily total utilization in column H:
=SUMPRODUCT($B$45:$G$45,$B$10:$G$10)/SUM($B$10:$G$10)

Does this help?