Create a custom max value from pivot table

Hi,

I have a data consist of rainfall value from day to day basis for 10 years in a row. For simple task i can create a maximum value of them using pivot table.
My question is, what if i want to create a 2-day or 3-day maximum value using pivot table? I can make it easily by a standard manual table, but i don't now how to do it in pivot table (i attached the sample what i want).

Appreciate for any help.

Regards,

Thank you for your respond AVK,

But after i tried that formula, the result is different from using Max(sum) formula. it tends to get lower value result.

Dear Irpanch,

if i understand correctly, you mean bij 2 and 3 day max, the SUM of the two/three highest rain days.

E.g. when the highest two are 10 and 8, you want it to show 18?

yes, just like that. for a simple table i can use a max(sum) formula. But i don't know how to do it in pivot table.

how to say,

according to your sample. when say, day one and day 4 have the two highest rainfalls.

Do you wish the "2 day high" to SUM them? or do you want it to check the surrounding dates to find a maximum of the 2 days subsequently?

I hope you understand my question.

StefanD

The calculation field allows you to apply basic operations such as: addition, subtraction, division, multiplication and functions IF

Originally Posted by StefanD

how to say,

according to your sample. when say, day one and day 4 have the two highest rainfalls.

Do you wish the "2 day high" to SUM them? or do you want it to check the surrounding dates to find a maximum of the 2 days subsequently?

I hope you understand my question.

StefanD

Okay, i just realized my mistake by reading your comment. So, here's right concept of 2-day max and 3-day max:
e.g. I have 5 days data in a row just like this:

1 3
2 0
3 5
4 36
5 27

Where as column 1 is day and, column 2 is value.
2 day max means, find the maximum sum value between 2 days in a row data. It can be: day 1 + day 2 (3), day 2 + day 3 (5), day 3 + day 4 (41), and day 4 + day 5 (63). The answer is 63. (as you can see, on my first example i'm not count an overlapping value and that was terrible mistake. Sorry )
moreover, 3 day max can be find by: day 1 + day 2 + day 3 (8), day 2 + day 3 + day 4 (41), and day 3 + day 4 + day 5 (68). The answer is 68.

Hope you get a better understanding about it.

Originally Posted by JeteMc

This proposed solution employs helper columns (V:Y is attached file). The formula that populates all helper columns is:

Formula:

Please Login or Register  to view this content.

In the formula V3 references the number of days.
The MAX function is then referenced toward the appropriate helper column to display the values in G7:G8
Let us know if you have any questions.

Great! it works perfect.
i'm really appreciate it.

is it possible to use your formula on a pivot table?

okay, here's the data. Thank you very much.

yes, this is what i want..
thank you.

i'll mark it solved.