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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[trone77]

I'm using 2019

 

[Fluff]

Thanks for that, please don't forget yo update your account details.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	77				77				77
3	76				76				77
4	82				82				77
5	74				74				77
6	74				74				77
7	72				72				77
8	73				73				77
9	74				74				77
10	65				76				77
11	78				78				77
12	78	Max	82		78	Max	82		77	Max	77
13	75	Average	75		75	Average	75		77	Average	77
14	76	Min	65		70	Min	70		77	Min	77
15		85				82				78
16
Main

Cell Formulas
Range	Formula
C12,K12,G12	C12	=MAX(A2:A14)
C13,K13,G13	C13	=ROUND(AVERAGE(A2:A14),0)
C14,K14,G14	C14	=MIN(A2:A14)
B15,J15,F15	B15	=MAX(C12-C13,C13-C14,1)+C13

 

[trone77]

Thanks, will do. One quick question. How would that formula look if I were to just use the column $A$2:$A14? I was using {=IF(MAX(A2:R14)-AVERAGE(A2:A14)>=AVERAGE(A2:A14)-MIN(A2:A14),MAX(A2:A14),(AVERAGE(A2:A14)-MIN(A2:A14)+(AVERAGE(A2:A14))))}

 

[Fluff]

How about

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

 

[trone77]

It works except for a few scenarios, for instance when the differences of both are equal, (Max 82 Average 75 Min 68)...that formula still returns a value of 83 instead of 82. And when all of the values are the exactly the same it doesn't add 1 to the max. Thanks again in advance for you help.

 

[Fluff]

Works for me

+Fluff 1.xlsm
	A	B	C	D					I	J	K
1
2	77								77
3	76								77
4	82								77
5	74								77
6	74								77
7	72								77
8	73								77
9	74								77
10	68								77
11	78								77
12	78	Max	82						77	Max	77
13	75	Average	75						77	Average	77
14	76	Min	68						77	Min	77
15		82								78
16
Main

Cell Formulas
Range	Formula
C12,K12	C12	=MAX(A2:A14)
C13,K13	C13	=AVERAGE(A2:A14)
C14,K14	C14	=MIN(A2:A14)
B15,J15	B15	=MAX(MAX(A2:A14)-AVERAGE(A2:A14),AVERAGE(A2:A14)-MIN(A2:A14),1)+AVERAGE(A2:A14)

 

[trone77]

Ok try replacing the previous number with these:

77
76
82
75
74
72
73
75
76
78
78
75
68

 

[Fluff]

That is down to the fact that the average is not a whole number. If you want to avoid that you will need to use round as I initially showed.