Need help - how to acces columns based on row information

[Amalainic]

Hello!

For my project, I need to create  a new column that calculates a weigthed average yearly. The problem is, sometimes I only need to start the yearly average from a certain month (for example, May in the third row of my example), or end at a certain month, or take only certain months between some dates into consideration (like the 4th row of my example). "Not Available" means that there's no start/end month, so I take all months into the calculation.

How can I reference certain columns to include in my average, based on the input of Start/End month?

[shreyanshrathod]

Hi @Amalainic ,

The attached solution should help you.

You can modify the solution as per your requirement.

Note :- I have extracted the month number (1-12). Since, my month names are abbreviated, I have used 'Mon' format to give me month number.

You will have to modify according to your input columns. 

E.g. If your columns are named "January", you will have to use 'Month' as your format.

Getting the month number is extremely essential.

Also, I have not considered for "Not available' scenario, but you can simply add a formula tool beforehand and replace those 'Not available' values with your specific requirement.

E.g If start month is not available then you can fill in 'January' and End month is not available, you can fill ' December'.

I am providing you a base upon which you can build your solution.

Regards,

Shreyansh Rathod

[mattnason1]

Here's how I'd do it.

[Amalainic]

Thank you for your input. The weighted average needs to be calculated using this logic : I multiply the value of each month with it's respective number of days in that month, sum all of these together and then divide by the total number of days.

Example: I have data for January, February and March 2020. January has a value of 40, February 60 and March 100. I will get the weighted average like this:   (40*31 + 60*29 + 100*31) / 91 = 66.81

I have joined your result of the formula (the one before the Summarize) with data about each month's number of days. How could I get, for each record ID, that number using the logic above? 

Please see the attached file.

Thank you!