[Solved] Identify which the row in one column to display

[michaelgingras]

Greetings
Looking for help. In the sample provided I am comparing two time periods 2018 vs 2017 broken into biweekly segments.
Column C and F are Average sale. Column D and G are a running average of column C and F. Best I knew how to do it.
Column D in cell D29 is indexed to show the last populated cell.
Below all that is a Scorecard that shows the Year to date averages for 2018 vs same time period last year in 2017.
I have been doing this manually but there must be a way to get F32 to self populate based on what is in the E32.
It would need to change every time new data is entered. Right now it should be looking to G22.
Can you offer some guidance?

[RusselB]

Welcome to the forums.
For more accurate results, I suggest using a couple of VLOOKUP formulas.
Specifically, I suggest putting

Code: Select all

=VLOOKUP(TODAY();B3:D28;3)

into E32. This would also allow you to eliminate the entry in D29
I also suggest putting

Code: Select all

=VLOOKUP(DATE(YEAR(TODAY())-1;MONTH(TODAY());DAY(TODAY()));E3:G28;3)

into F32

[michaelgingras]

Yes, this is close to what I need. fantastic help. It almost works as you placed it. I attached the results.
F32 should be displaying the same as G21. It is showing me G24 which is the cell closest to today's date.
Any idea for to show me the last entered in C changing the last average in D.
E32 should read the same as D21 in this example,
and F32 should read the same as G21. I hope I'm explaining this correctly.
E32 and F32 should change when a new bi-weekly is entered in C, not by the day viewing the spreadsheet.
Thank you

[RusselB]

E32 and F32 should change when a new bi-weekly is entered in C, not by the day viewing the spreadsheet.

This is new information and I'm not sure how it could be handled, as there's no information in the spreadsheet for Calc to reference in order to determine if it's been two weeks since the last update.
With today's date being 18-11-06 (using the format in your spreadsheet), then E32 would match D24, and since a year ago the date would've been 17-11-06, F32 would match G24 as the date in E24 is the closest date that is before 17-11-06.

Can you explain why F32 should be showing the information from G21, which has a relevant date of 17-09-21 (Sept 21, 2017)? Even using a date two weeks before (referencing last years information) would be a date of 17-10-23, which would be row 23.

Actually, looking at your spreadsheet again while typing this, makes me think that you're using the numbers in column A as your row reference numbers, rather than the actual row numbers, which, in this case, are greater than the number you are referencing by 2.

If that is correct, then use

Code: Select all

=VLOOKUP(TODAY()-14;B3:D28;3)

in E32 and

Code: Select all

=VLOOKUP(DATE(YEAR(TODAY())-1;MONTH(TODAY());DAY(TODAY())-14);E3:G28;3)

in F32.
That should handle the change in the information regarding the actual dates you wanted to reference in the chart.

[thinman3]

Well, not sure if this gets you any closer to the solution you are wanting, but see if the attached file works for you.

**removed file a/o 11-08-18

[morchat]

The first problem concerns the VLOOKUP function. The first dates in these areas are 13/01/2017 and 14/01/2018. Dates older than these will cause an error because the first argument can not be less than the smallest value in the first column of the search area.
You wrote that the data for the last period entered in the year should be compared.
I suggest a different solution.
The two-week periods to be compared should always end on the same day, so it is worth setting them permanently. Therefore, the week containing the date of 29 February (leap-year) will be one day longer. Also the last week of the year will be one day longer.
The example is in Sheet2.

[morchat]

Sorry but solution given by thinman3 is not good. The VLOOKUP function uses the FALSE fourth argument (find an exact match), but in the area of D3: D28, the same value may appear several times. Then, the first one found is matched as the match.

[MrProgrammer]

Hi, and welcome to the forum.

I have been doing this manually but there must be a way to get F32 to self populate based on what is in the E32.

=INDEX(G3:G28;COUNT(C3:C28))

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know