I need two formula. Essentially I need an if statement that adds the greater difference of the two values, (Max - Average) or (Average - Min). I would like for the first formula to include the rules in Ex. 1 & Ex. 2. And for the second formula to include all three.
Ex. 1
77
76
82
74
74
72
73
74
76
78
78 Max 82
75 Average 75
70 Min 70
If the difference of Max (82) - the Average (75) is greater than or equal to the difference of the Average (75) - the Min (70), then simply add the difference of (7) to the Average (75), returning the value of (82).
Ex. 2
77
76
82
74
74
72
73
74
65
78
78 Max 82
75 Average 75
76 Min 65
If the difference of Average (75) - the Mn (65) is greater than or equal to the difference of the Max (82) - the Average (75), then simply add the difference (10) to the Average (75). Returning the value of (85).
Ex. 3
77
77
77
77
77
77
77
77
77
77
77 Max 77
77 Average 77
77 Min 77
And for the second version of the formula, I need an additional rule to be added. If all the values are the exact same to simply return the Max + 1, returning the value of (78).
Ex. 1
77
76
82
74
74
72
73
74
76
78
78 Max 82
75 Average 75
70 Min 70
If the difference of Max (82) - the Average (75) is greater than or equal to the difference of the Average (75) - the Min (70), then simply add the difference of (7) to the Average (75), returning the value of (82).
Ex. 2
77
76
82
74
74
72
73
74
65
78
78 Max 82
75 Average 75
76 Min 65
If the difference of Average (75) - the Mn (65) is greater than or equal to the difference of the Max (82) - the Average (75), then simply add the difference (10) to the Average (75). Returning the value of (85).
Ex. 3
77
77
77
77
77
77
77
77
77
77
77 Max 77
77 Average 77
77 Min 77
And for the second version of the formula, I need an additional rule to be added. If all the values are the exact same to simply return the Max + 1, returning the value of (78).