If formula

[shadow12345]

Someone help me out please, its just an IF, but my brain is broken.


I have be below formula working fine. I just need to add one more check but I can't work it out.

I need to say if the result of the below is less than Col F (the row for below is F5) put col F value,

OR
the result of the below is more than Col J (the row for below is J5) put col J value

=IF(OR(F5=0,J5=0),D5+C4,IF(J5-F5>$B$16,D5+C4,ROUNDUP(MEDIAN(F5,J5),0)))



Example data. The above formula is in B5.
<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>A</b></td><td align="center" bgcolor="#C0C0C0"><b>B</b></td><td align="center" bgcolor="#C0C0C0"><b>C</b></td><td align="center" bgcolor="#C0C0C0"><b>D</b></td><td align="center" bgcolor="#C0C0C0"><b>E</b></td><td align="center" bgcolor="#C0C0C0"><b>F</b></td><td align="center" bgcolor="#C0C0C0"><b>G</b></td><td align="center" bgcolor="#C0C0C0"><b>H</b></td><td align="center" bgcolor="#C0C0C0"><b>I</b></td><td align="center" bgcolor="#C0C0C0"><b>J</b></td><td align="center" bgcolor="#C0C0C0"><b>K</b></td><td align="center" bgcolor="#C0C0C0"><b>L</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="166.5" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Jul-11 </font></td><td rowspan="1" colspan="1" width="168" height="30" align = "right" valign = "bottom" bgcolor ="#FFCC99"><font color="#3F3F76">2422 </font></td><td rowspan="1" colspan="1" width="130.5" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">-40 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFCC99"><font color="#3F3F76">2462 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFCC99"><font color="#3F3F76">2421 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#C6EFCE"><font color="#006100">2421 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFCC99"><font color="#3F3F76">2421 </font></td><td rowspan="1" colspan="1" width="141" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2421 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFCC99"><font color="#3F3F76">2423 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#C6EFCE"><font color="#006100">2423 </font></td><td rowspan="1" colspan="1" width="96" height="30" align = "right" valign = "bottom" bgcolor ="#FFCC99"><font color="#3F3F76"> </font></td><td rowspan="1" colspan="1" width="141" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2424 </font></td></tr>
</table>

 

[ramsee]

Put this " IF(OR(F5=0,J5=0),D5+C4,IF(J5-F5>$B$16,D5+C4,ROUNDUP(MEDIAN(F5,J5),0))) " in for X

=IF(X>f5,f5,if(X>j5,j5))