IF condition

churmie

New Member
Joined
Jan 9, 2014
Messages
44
I need help with what should be easy formula but I can't get it right. I have the following figures:

22001000

<tbody>
</tbody>

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!
 
Last edited by a moderator:

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hello,

Based on your example ... what is your expected result ?
 
Upvote 0
Perhaps...

=Max(average formula, minimum formula)
 
Upvote 0
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.
 
Upvote 0
how about?


Book1
ABCDEFG
122001000
2222.52.522.52.5
Sheet1
Cell Formulas
RangeFormula
A2=IF(A1=0,MAX(AVERAGE($A$1:$G$1),2.5),AVERAGE($A$1:$G$1))
 
Upvote 0
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)
 
Upvote 0
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
 
Last edited:
Upvote 0
Quite honestly ...

It is not clear at all ...

Replacing zero values by any number ... will change your average ... and create a circular reference ...
 
Upvote 0
ok, try this instead


Book1
ABCDEFG
122001000
2222.52.5102.52.5
3
42.5
Sheet1
Cell Formulas
RangeFormula
A2=IF(A1=0,MAX(AVERAGE($A$1:$G$1),$A$4),A1)
A4=10/4
 
Last edited:
Upvote 0
ABCDEFGHIJKLMNOPQRSTUV
1MonTueWedThuFriSatSunMin StockAverageMonTueWedThuFriSatSun
2220010002.54.7224.74.7104.74.7

<tbody>
</tbody>


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

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top