I have an Excel table that has a column named 'month name', which then has a row for every day in that month. Theres also a column for rainfall, which shows the amount of rainfall in each day of the month. How can I get the user to select a month, and then show the maximum and minimum rainfall for that month?
2 Answers
To Find The Max Or Min Value Based On Multiple Criteria:
Enter this formula into a cell you want place the result : =MAX(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13)))
In this formula: A2:A13 is the data which contains the criteria1, B2:B13 is the data range which contains the criteria2, F1 and F2 are the criteria you are based on, C2:C13 refers to the range you want to return the max value. Then press Ctrl + Shift + Enter keys simultaneously, the max value where the corresponding value in A2:A13 equals the value in F1, and the corresponding value in B2:B13 equals the value in F2 is returned.
To get the min value based on these criteria, please apply this formula: =MIN(IF(A2:A13=F1,IF(B2:B13=F2,C2:C13))), and remember to press Ctrl + Shift + Enter keys together.
To give a method for the user to select a month you can use data validation.
Either have a list of the 12 months in cells or you can enter them direct in the data validation editing box. Select the list type and you end up with a cell with a drop-down arrow.
On a smartphone so no image possible.
Related