Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying a formula to multiple cells | Excel Discussion (Misc queries) | |||
applying a formula to an entire spreadsheet | Excel Worksheet Functions | |||
Applying Percentages to a Pivot Table | Excel Discussion (Misc queries) | |||
applying formula | Excel Discussion (Misc queries) | |||
Applying colour to my Formula | Excel Discussion (Misc queries) |