Excel Problem - Updating Yield Calculation

Microsoft Office 2007 professional (aca...
February 4, 2010 at 15:28:16
Specs: Windows Vista
Hi there,

I'm a novice at Excel, so I hope someone can help me.

I have a workbook with 13 sheets (Jan to Dec + Summary Sheet).

Each day of the year, a yield figure is calculated by dividing the gross rental figure by the number of units rented.

How can I get the summary sheet "average yield value" to match the latest "average yield value"?

I really hope that makes sense. It's got me totally stumped!!

Any help sincerely appreciated.

See More: Excel Problem - Updating Yield Calculation

Report •

February 4, 2010 at 17:46:19
Could you provide us with a little more information?

Where are these yield calculation stored? How are you currently calculating the "average yield" on the summary sheet?

The more details you provide, the easier it will be to help you.

Report •

February 4, 2010 at 18:07:34
Sorry, I'll try to explain in a little more depth:

The workbook has 13 sheets - all identical in format, but for the dates.

Sheet 1 = Jan 1st - 31st.
Sheet 2 = Feb 1st - 28th.

We rent out rooms of different sizes and each day I input:

1: The occupied square footage
2. The rental income. (based on the first figure being static for 1 month)

The yield per sq.ft is calculated by rent divided by occupied space.

Because of this, the yield figure differs daily. What I need is the summary sheet to find the most recent figure from the 12 monthly sheets and display it accordingly.

I'm thinking something along the lines of:

=IF('January 2010'!R27<1,'January 2010'!Q27,'January 2010'!R27)

But repeated.....

As in if December the 31st is blank, then look at December the 30th. If that's blank, then look at 29th, etc, etc. All the way to January 1st.

I'm pretty sure I'm a million miles away from the correct way to do it, but I hope that explains what I want to achieve a little better.

Report •

February 5, 2010 at 05:26:04

Rather than search for the last value, you could calculate where the last value is and pull it straight in.

If Each month sheet has a tab name in the format 'January 2010'
And each days average is in row 27, with day 1 in column A (cell A27), day two in cell B27 and so on to the end of the month. The 30th would be in cell AD27.

On your summary page use this formula:
=INDIRECT("'" &TEXT(TODAY(),"mmmm")
&" "&TEXT(TODAY(),"yyyy")&"'!" & "R27C" & TEXT(TODAY(),"d"),FALSE)

The formula is split onto two lines for ease of viewing.

The formula calculates the Tab name (month and year) and uses row 27 and calculates the column number from the day.

Instead of using TODAY(), you could reference another cell containing a date, then you could easily look at yesterday's result, or today's result, for example, just by changing the date.

You could give the cell containing the date, a drop-down list to choose from, say Today's date and the previous 7 days. Use data validation and the list option. The list is created starting with =Today() in say cell A18, then A19 is =A18-1, A20 is =A19-1 and so on. Format the cells in a suitable date format.

Hope this gives you an idea for how you can solve your problem.


Report •

Related Solutions

February 6, 2010 at 17:04:44
Sorry to be a pain Humar,

I struggled with the above and a couple of other things, so I've now simplified the input sheet - the full year is now on one sheet with the summary page being on the next sheet.

My Yield is now in column "T" and the Date column is Column "C".

I'm hoping that will make the process of extracting the latest yield figure easier [for me at least] to understand. Any explanative notes you could add would be sincerely appreciated!

Report •

February 7, 2010 at 05:32:03

As I understand it, your daily yield values throughout the year will be in column T on one worksheet.

I have used Sheet2 for this example. If you have given it a different name, you will have to replace Sheet2 in the formula with the name.

Assuming that the yield for 01 January is in cell T2 (I left row 1 for a column heading),

then you can use the VLOOKUP() OR INDIRECT() functions along with some date calculations to identify the cell in column T that contains today's yield.

On Sheet1, your summary sheet, enter this formula in the cell that will show today's yield:

=INDIRECT("'Sheet2'!T" & TEXT(TODAY()-40179+2,"#0") )

The function TODAY() returns a number which represents today in Excel's date system (1 was 01 January 1900 and 40179 was 01 January 2010)
TODAY()-40179 returns the difference in days between today and the first of January.

As your data starts in row 2, and because we only got the difference in dates we have to add an extra 2. If the yield data started on row 3 you would make the adjustment value 3.

The TEXT() function takes the numerical output of the days calculation and converts it to text using a formatting string. These are the same as those used in custom formatting cells. The "#0" means that 9 will become 9 and 10 will become 10. If you used "00" 9 would become 09.

Putting everything together inside the INDIRECT() function you get something like this:

INDIRECT returns the value in cell T38 on Sheet2.

As a result INDIRECT() will return the value from a different cell in column T on Sheet2, each day.

I went through this to show you how the previously posted formula worked, except that in the previous one it also created the name of the worksheet such as 2010 January and used an alternative cell addressing system where cell B1 is R1C2. Hence the FALSE at the end of the INDIRECT() function, making it use RC type addresses, which made it easier to get a column - using numbers rather than letters.

OK, so as you have all the data for the year on one sheet with dates in column T, you can just use VLOOKUP() to return the yield for Today.

On Sheet1 use this formula:
VLOOKUP() will look for today's date in column C, rows 2 to 366 on Sheet2, and return the value from the cell on the same row as the matching date, but 17 columns to it's right.
A value of 1 would return the date found (not much use), i.e., 1 is column C in this case. 18 is column T.

If you wanted to be able to get the result for different days, not just today, use this formula where cell D20 contains a date.

To make this work enter =Today() in cell E20
and in cell E21 enter =E20-1, and drag this down to row E26
You will now have a list of dates for the last seven days (format these cells and D20 in a suitable date format)
Click on cell D20 and select Data Validation ( I don't have Excel 2007 on this PC, so I can't guide you through the Ribbon). When you get the data validation dialog box open, select List from the 'Allow:' drop-down.
In the 'Source:' box use the selection button at the end of it, to select cells E20 to E26. Click OK
Now goto cell D20 and it will have a drop-down list starting with today's date and the dates for the last week.

Select a date and the VLOOKUP() function will return the yield value from column T, on Sheet2 for that date.

Hope this helps.

PS not a pain at all - the forum is here to help provide answers.

Report •

February 9, 2010 at 15:16:49
Hi again Humar,

I think I'm getting my head around all of this bit by bit!


(albeit little bits .. very little bits)....

Following from the above. I've now got 1 data sheet (sheet2), one intermediary sheet (sheet1) and one summary sheet.

The intermediary sheet contains the 52 Monday dates (A4:A55), Colums B4:B55, C4:C55, D4:D55, ETC. Represent the totals for those weeks under various headings (Sales, Movement, Yield, Etc) (so far so good), BUT...

On the summary sheet I then went to pull the latest figure for my (week/month/year) figures and am lost as to how to tell it what to grab.

I guessed at:


And quickly concluded it was a poor guess (I'm guessing because in the 52 dates covered, there isn't always a "today?")

Educate me at your leisure...


Report •

February 9, 2010 at 15:53:10

I suspect that you are right - A Monday will only match TODAY() 1 out of 7 times.

The trick might be to use TODAY() with it's weekday number.

WEEKDAY(TODAY()) returns 1 for Sunday, 2 for Monday etc., anyway that's the default.
So we can use the day number with an adjustment to get the previous Monday.

This formula will always return the prior Monday:
If today is Tuesday the weekday number is 3 so Today -3 + 2 is the day before Tuesday which was Monday !

Replace TODAY() in your VLOOKUP with
and see if it works.


Report •

February 10, 2010 at 12:44:40
Sadly, no!

Formula = =VLOOKUP(TODAY()-WEEKDAY(TODAY())+2,Sheet1!A4:B55,2,FALSE)


A4 = 04/01/2010
B4 = Number Of Enquiries Week Commencing 04/01/2010
Both On Sheet 1

Output = 0 (B4 actually = 12)

Any more thoughts?

Report •

February 10, 2010 at 12:59:50

The part of the formula


returns 08 Feb (Mon, 08/Feb/2010).

Did you have data for that date.


Report •

February 10, 2010 at 13:40:48
Shoot me now and shoot me good...

How could I miss something so fundamental?

(don't answer that.....)

So anyway,

Is there another such method for extracting the latest MTD Figure?

I have January (J4) - December (J15) - Sheet 1.

All the figures seem to be adding up nicely from the data sheet into the intermediate sheet. It's just pulling the latest Monthly figure into the MTD figure in the summary sheet.

Similarly with the YTD If possible.

And I think that would actually conclude this exercise!!! To be honest, I would happily sit and present you with a million other problems since you've opened my mind to the wealth of possibilities, but I can't help thinking I'm taking your time from the rest of the community.

Again though, I truly cannot thank you enough!

Report •

February 10, 2010 at 14:55:31

Try this for the first of the current month
It uses EOMONTH with -1 to get the last day of last month, and
+1 to move to the first day of the next (i.e., this) month.

For first day of the year I used
just taking the Year value from today's date and using first month and first day.

Thank you also for your kind words.

I enjoy solving issues with Excel which I have used for a long time. I still have the install disks for Excel 4.0 from about 1992, (it was on 5 floppies !).

Also working on other people's issues has been a great learning experience for me, to say nothing of what I learn from other contributors.

Good luck with your current project. Once you have it working, you will soon want to add a few bells and whistles to it and then some ...


Report •

February 11, 2010 at 13:19:09
That generates the first day of the month and year, but at the risk of sounding obtuse, how do I apply that to my existing data to get the summary to update the various figures figures daily.

Ignoring my previous post, if my dates are on sheet 2 C3:C367, what formula would represent MTD & YTD when applied to the formulas you gave above?

It's quite apparent that you've worked with Excel for many years! Is/are there any particular resources you'd recommend for developing an understanding of it? I must admit, I'm quite taken by its power and the challenges of understanding it!

Report •

February 12, 2010 at 05:31:54

When you are looking up results for each week you used:
This means that on Sheet1 you have a series of dates in column A and weekly totals in column B

To use your same plan for months, you will need a series of months in a column (each date is the first of the month), with the monthly totals in the next column.

If the months are in column C and the monthly subtotals are in column D then the formula is

You should be able to follow the same pattern you used to create the week totals from the daily data to create the month totals from the daily data.

As to a good source of information on Excel, I have been asked that before, and I don't have an answer. I do use the function help a lot. Enter = in a cell and then in the box to the left of the formula bar there is a function drop-down list. From the drop-down select More functions ...and use the search for a function box, or select a group of functions related to what you are trying to do. This often points you to a function that will do what you want.

Sorry but not a lot of help on this issue as I have just learnt by trial and error rather than from any books or specific resources.

As a general rule I have found that it is worth spending time on deciding how to store and organize your raw data. Consider how the data will look when you add another years worth of data or whatever - will you be able to use the same logic to access the data when it gets larger. Use structures which can easily be repeated. If you have annual data, use a new sheet for each year and always put totals and subtotals in the same cells on each sheet - it makes it so much easier to create summary sheets which can easily be expanded for the next year. Also think about what you are going to want to get out of the data, it helps you to decide how to organize the raw data.


Report •

Ask Question