Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Applying a formula for daily percentages

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Applying a formula for daily percentages

Fido wrote:
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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Applying a formula for daily percentages



"smartin" wrote:

Fido wrote:
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.




"smartin" wrote:

Fido wrote:
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.


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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Applying a formula for daily percentages

Fido wrote:

"smartin" wrote:

Fido wrote:
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.




"smartin" wrote:

Fido wrote:
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.


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(B2:D2)

On each day row starting in H3 and filling down, the daily total
utilization is
=SUM(B3:D3)/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)
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Applying a formula for daily percentages

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?k...1NDO Xc&hl=en


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 915
Default Applying a formula for daily percentages

Fido wrote:
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?k...1NDO Xc&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?
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Applying a formula to multiple cells Lindsay Excel Discussion (Misc queries) 2 April 2nd 09 10:34 PM
applying a formula to an entire spreadsheet michelle Excel Worksheet Functions 3 October 25th 08 05:11 AM
Applying Percentages to a Pivot Table Dan Excel Discussion (Misc queries) 0 March 16th 07 05:39 PM
applying formula Pat N. Excel Discussion (Misc queries) 1 April 25th 06 08:47 PM
Applying colour to my Formula Ben Excel Discussion (Misc queries) 7 March 9th 06 05:30 PM


All times are GMT +1. The time now is 07:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"