Formula with multiple Conditions...

[trone77]

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).

 

[trone77]

Ok I see. How about this, just to explore all options, if in the even that both differences are equal, could we just do a standard max of that column. How would that formula look?

 

[Fluff]

If you do not round the Average then it's highly unlikely that both differences will be equal.

 

[trone77]

Totally understand, but I need the average rounded to a whole number.

 

[Fluff]

Which you can do like

=MAX(MAX(A2:A14)-ROUND(AVERAGE(A2:A14),0),ROUND(AVERAGE(A2:A14),0)-MIN(A2:A14),1)+ROUND(AVERAGE(A2:A14),0)

 

[trone77]

Perfect!!! I Really appreciate your help.

 

[Fluff]

Glad to help & thanks for the feedback.