IF condition

[churmie]

I need help with what should be easy formula but I can't get it right. I have the following figures:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The average is 2, but I have a separate Formula which is a minimum of 2.5. How do I replace the zero values with whichever is higher of the average or the minimum?

thanks!

 

[James006]

Hello,

Based on your example ... what is your expected result ?

 

[Yongle]

Perhaps...

=Max(average formula, minimum formula)

 

[churmie]

Hi James

The result I would expect is 2.5.

Yongle - The max formula doesn't work how I want it to. I only want to change cells with a zero value. I tried what you had but it changed values to a higher number when it wasn't a zero.

 

[AlanY]

how about?

Book1
	A	B	C	D	E	F	G
1	2	2	0	0	10	0	0
2	2	2	2.5	2.5	2	2.5	2.5
Sheet1

Cell Formulas
Range	Formula
A2	=IF(A1=0,MAX(AVERAGE($A$1:$G$1),2.5),AVERAGE($A$1:$G$1))

 

[Yongle]

I do not understand what is being tested, but in principle
=IF(something = 0,MAX(AVG formula, MIN formula), something)
Something may be the same cell ref or the same formula)

 

[churmie]

Thanks Alan, but cell E1 changes the 10 to a 2 which I do not want. I only want to change the zero's. The figure 2.5 is a separate formula but ca also change dependent on the worksheet I am looking at

A B C D E F G MIN AVE
1 2 2 0 0 10 0 0 2 4.7
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"> 0002ABCDEFG1220010002222.52.522.52.5</body>2 2 2 4.7 4.7 10 4.7 4.7

i need it to look like this.... Sorry for not being clear

 

[James006]

Quite honestly ...

It is not clear at all ...

Replacing zero values by any number ... will change your average ... and create a circular reference ...

 

[AlanY]

ok, try this instead

Book1
	A	B	C	D	E	F	G
1	2	2	0	0	10	0	0
2	2	2	2.5	2.5	10	2.5	2.5
3
4	2.5
Sheet1

Cell Formulas
Range	Formula
A2	=IF(A1=0,MAX(AVERAGE($A$1:$G$1),$A$4),A1)
A4	=10/4

 

[churmie]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Min Stock[/TD]
[TD]Average[/TD]
[TD][/TD]
[TD]Mon[/TD]
[TD]Tue[/TD]
[TD]Wed[/TD]
[TD]Thu[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2.5[/TD]
[TD]4.7[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4.7[/TD]
[TD]4.7[/TD]
[TD]10[/TD]
[TD]4.7[/TD]
[TD]4.7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sorry, let's put it in a better table. Columns P-V is what I want the results to look like. Column P did not change value because it has a value already. The zero needs to be replaced with the greater of the Min Stock or Average (Column M & N).