Using Table Names in Average Formula Using Unconventional format

[cosmica67]

Hi, All.


I have attached some dummy data, which I need some help with on two sections of the spreadsheet, please.


Section 1

I have 3 Results Tabs, which contain data and are averaged in cells (column M).

At the moment, the Tracking Tab uses ranges to extract the data from the Results Tabs.

I would like the formulas in F12:F14 to use the corresponding Table Names instead of ranges.

Can anyone suggest a formula that would work using table names?



Section 2

In each of the 3 Results Tabs, the formula in I2:K4 use data ranges to find the average of the data for a particular Year (2016-2017, 2017-2018, 2018-2019).

Is there a way to get the same results using a LOOKUP function instead of a data range or will I need to stick with data ranges because of the unconventional format (eg 6/11, which equates to 6 Years/11 Months).



Many thanks for looking.

[WideBoyDixon]

In O365, the following works for F12:

Please Login or Register  to view this content.

Don't know whether that will work in your version or not. For the second part, things would be easier in O365 but the format of your data is not helping and I can't think of a way to simplify without using helper columns.

WBD

[cosmica67]

Hi, WideBoyDixon,

Yes, that formula works perfectly in my version of Excel as well!!!!

That's section 1 sorted with the suggestion you gave me.

Many thanks!

[Bo_Ry]

Please try

1 F12
=VLOOKUP(D12,INDIRECT("Table"&6+C12&"[[Year]:[Average]]"),7,)

2 I2
=SUBSTITUTE(TEXT(SUMPRODUCT(MID(SUBSTITUTE(C$2:C$51,"/"," "),{1,4},3)*{12,1}*($B$2:$B$51=$G2))/12/COUNTIFS($B$2:$B$51,$G2),"0\/0/12"),"/12",)

[cosmica67]

Hi, Bo_Ry.

Both of these formula suggestions also work perfectly.

Many thanks for your input!!!

[cosmica67]

Hi, Bo_Ry.

I have come across a problem that I hope you can suggest a workaround for.

In the dummy data I provided, there were no blank cells but in the data I have, there are blank cells.

When I use the formula that you suggested for Section 2:

=SUBSTITUTE(TEXT(SUMPRODUCT(MID(SUBSTITUTE(C$2:C$51,"/"," "),{1,4},3)*{12,1}*($B$2:$B$51=$G2))/12/COUNTIFS($B$2:$B$51,$G2),"0\/0/12"),"/12",)

it works but only when there are no empty cells.

I have included the sample file with blank cells so you can see what I mean.

Thanks for looking.

[Bo_Ry]

Please try at I2

=SUBSTITUTE(TEXT(SUMPRODUCT(TEXT(MID(SUBSTITUTE(C$2:C$51,"/"," "),{1,4},3),"0;;0;\0")*{12,1}*($B$2:$B$51=$G2))/12/COUNTIFS($B$2:$B$51,$G2,C$2:C$51,"<>"),"0\/0/12"),"/12",)

[cosmica67]

Hi, Bo_Ry.

That's great - the suggestion works efficiently in the dummy data.

I'll transfer it over to my original data sheet and hopefully, it'll work just as well.

Many thanks once again!

[cosmica67]

Hi, Bo_Ry.

I have used the formula you suggested and it works perfectly even when there are blank cells, which is great.

I have come across another problem, where I am getting a #VALUE! error and I wondered if you could take a look, please?

When I have any negative value in a cell, for example -5/8, I get this error.

I have attached the same sample file with a couple of negative values in the Results 1 Tab.

Many thanks for looking.

[JeteMc]

This proposal employs four helper columns.
Columns E and G display the years using: =IF(C2="","",LEFT(C2,SEARCH("/",C2)-1)+0)
Columns F and H display the months using: =IF(C2="","",RIGHT(C2,LEN(C2)-SEARCH("/",C2))+0)
The columns for September and March averages are populated using: =ROUND(AVERAGEIFS(Table1[Sept Yrs],Table1[Year],K2),0)&"/"&ROUND(AVERAGEIFS(Table1[Sept Mos],Table1[Year],K2),0)
Let us know if you have any questions.

[cosmica67]

Hi, JeteMc.

Many thanks for your suggestion, which I have now had time to digest and get my head around.

Using the four helper columns certainly seems to fix the error message I was getting when there are any negative numbers, so this is great.

With the average data (column Q) is there any way that the ranges in the formula can be changed to use table names instead or is there a reason for using these ranges?

Thanks for looking.

[JeteMc]

It appears that Bo_Ry's array formula** in column Q can be rewritten using table names (aka. structured references):

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
This regular formula produces the same results as those shown in column Q:

Please Login or Register  to view this content.

Let us know if you have any questions.

[cosmica67]

Hi, JeteMc.

This is exactly what I was looking for.

Many thanks for your input and tweaking what Bo_Ry initially suggested.

Regards.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.