Formulas with altenating cells?

yani06 · Sep 26, 2011

My basic formula is this: =INT((E3-D3)/7)

but if E3>$G$9 then E3 should = $G$9

and if D3<$G$10 then D3 should = $G$10

and if E3 is blank then it should also = $G$9

Is there a way to merge all these and create one formula that I can drag across the column?

yani06 · Sep 26, 2011

Okay. I'm working from 2 tabs. One with the example and one with the formulas in view. This is the actual table. But I'm having a hard time showing the formula.

<TABLE style="WIDTH: 156pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=208 border=0 x:str><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><TBODY><TR style="HEIGHT: 30.75pt" height=41><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30.75pt; BACKGROUND-COLOR: transparent" width=75 height=41>Hired</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=79>Terminated</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>4 yr P/P</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10/26/2007</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/12/2008</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=INT((B2-A2)/7)" x:num>41</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5/4/2009</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>92</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2/8/1992</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>208</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2/25/2010</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40241">03/04/10</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=INT((B5-A5)/7)" x:num>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10/19/2009</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="40305">05/07/10</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=INT((B6-A6)/7)" x:num>28</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="40461">10/10/10</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>17</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11/27/2006</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8/21/2009</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=INT((B8-A8)/7)" x:num>142</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10/9/2006</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5/2/2008</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:fmla="=INT((B9-A9)/7)" x:num>81</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>6/23/2009</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>84</TD></TR></TBODY></TABLE>

And this is what that 4yr p/p looks like:
<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=108 border=0 x:str><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 1974" width=108><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 81pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=108 height=20 x:num="41">=INT((E3-D3)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="92">=INT(($C$17-D4)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="208">=INT(($C17-$C16)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="1">=INT((E6-D6)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="28">=INT((E7-D7)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="17">=INT(($C$17-D8)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="142">=INT((E9-D9)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="81">=INT((E10-D10)/7)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num="84">=INT(($C17-D11)/7)</TD></TR></TBODY></TABLE>

I'm sorry about all the confusion. And I really appreciate all the time and help!

yani06 · Sep 26, 2011

Maybe I can make it simpler. Let me see if I can explain myself better.

If D3 is before 2/7/07 date, then it should be the $G$10 value of 2/7/07.

If D3 equals or is after the 2/2/07 date, then D3 remains the same.

IF E3 is blank then it should reflect the $G$11 value of 2/7/11.

If E3 equals the value of $G$11 (2/7/11) or a date before, then it stays with the value of E3.

If result comes at 0, it should show always come up as 1.

This is just to calculate the G column from example above.

Does it make sense?

markmzz · Sep 26, 2011

Look at the cells in yellow:

I think that your's formulas isn't ok. The values in column D are before the value in the cell C16 (D9 and D10 are < C16).

Excel 2007<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">new formula</td><td style="text-align: center;;">your formula</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: center;;">10/26/2007</td><td style="text-align: center;;">8/12/2008</td><td style="text-align: center;;">41</td><td style="text-align: center;;">41</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: center;;">5/4/2009</td><td style="text-align: center;;"></td><td style="text-align: center;;">92</td><td style="text-align: center;;">92</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">2/8/1992</td><td style="text-align: center;;"></td><td style="text-align: center;;">208</td><td style="text-align: center;;">208</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;">2/25/2010</td><td style="text-align: center;;">3/4/2010</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;">10/19/2009</td><td style="text-align: center;;">5/7/2010</td><td style="text-align: center;;">28</td><td style="text-align: center;;">28</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: center;;">10/10/2010</td><td style="text-align: center;;"></td><td style="text-align: center;;">17</td><td style="text-align: center;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">11/27/2006</td><td style="text-align: center;;">8/21/2009</td><td style="text-align: center;background-color: #FFFF00;;">132</td><td style="text-align: center;background-color: #FFFF00;;">142</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: center;background-color: #FFFF00;;">10/9/2006</td><td style="text-align: center;;">5/2/2008</td><td style="text-align: center;background-color: #FFFF00;;">64</td><td style="text-align: center;background-color: #FFFF00;;">81</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: center;;">6/23/2009</td><td style="text-align: center;;"></td><td style="text-align: center;;">84</td><td style="text-align: center;;">84</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2/7/2007</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2/7/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">****</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table>Sheet3 <table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" >Worksheet Formulas<table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=INT((IF(OR(E3>$C$17,E3=""),$C$17,E3)-IF(D3<$C$16,$C$16,D3))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=INT((E3-D3)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=INT((IF(OR(E4>$C$17,E4=""),$C$17,E4)-IF(D4<$C$16,$C$16,D4))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=INT(($C$17-D4)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=INT((IF(OR(E5>$C$17,E5=""),$C$17,E5)-IF(D5<$C$16,$C$16,D5))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G5</th><td style="text-align:left">=INT(($C17-$C16)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F6</th><td style="text-align:left">=INT((IF(OR(E6>$C$17,E6=""),$C$17,E6)-IF(D6<$C$16,$C$16,D6))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G6</th><td style="text-align:left">=INT((E6-D6)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F7</th><td style="text-align:left">=INT((IF(OR(E7>$C$17,E7=""),$C$17,E7)-IF(D7<$C$16,$C$16,D7))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G7</th><td style="text-align:left">=INT((E7-D7)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F8</th><td style="text-align:left">=INT((IF(OR(E8>$C$17,E8=""),$C$17,E8)-IF(D8<$C$16,$C$16,D8))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G8</th><td style="text-align:left">=INT(($C$17-D8)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F9</th><td style="text-align:left">=INT((IF(OR(E9>$C$17,E9=""),$C$17,E9)-IF(D9<$C$16,$C$16,D9))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G9</th><td style="text-align:left">=INT((E9-D9)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F10</th><td style="text-align:left">=INT((IF(OR(E10>$C$17,E10=""),$C$17,E10)-IF(D10<$C$16,$C$16,D10))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G10</th><td style="text-align:left">=INT((E10-D10)/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F11</th><td style="text-align:left">=INT((IF(OR(E11>$C$17,E11=""),$C$17,E11)-IF(D11<$C$16,$C$16,D11))/7)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G11</th><td style="text-align:left">=INT(($C17-D11)/7)</td></tr></tbody></table></td></tr></table> 
Markmzz

yani06 · Sep 26, 2011

I most have a formatting problem because some formulas are not working. Will check back later.

markmzz · Sep 27, 2011

yani06 said:
Is there anywhere that would explain the formula?
Thanks!

I will try to explain the complete formula below.

=IF(INT((IF(OR(E3>$G$17,E3=""),$G$17,E3)-IF(D3<$G$16,$G$16,D3))/7),INT((IF(OR(E3>$G$17,E3=""),$G$17,E3)-IF(D3<$G$16,$G$16,D3))/7),1)

This part IF(OR(E3>$G$17,E3=""),$G$17,E3) do this:

if E3>$G$17 or E3 is blank then E3 is= $G$17

else E3 is =E3

this part IF(D3<$G$16,$G$16,D3) do this:

if D3<$G$16 then D3 is = $G$16

else D3 is =D3

This part IF(INT((IF(OR(E3>$G$17,E3=""),$G$17,E3)-IF(D3<$G$16,$G$16,D3))/7) do this:

Test if the result of the formula isn't 0

Then this part ,INT((IF(OR(E3>$G$17,E3=""),$G$17,E3)-IF(D3<$G$16,$G$16,D3))/7),1) do this:

if the result of the test isn't 0 then do the calc

else the result is 1.

I wait to have helped.

Markmzz

yani06 · Sep 27, 2011

Thanks. I'm trying to digest and understand it and will get back to you. I'm feeling like the left out nerd now, but hopefully I can come around.

I truly appreciate the time you have put into this!

jasonb75 · Sep 27, 2011

Condensing markmzz's formula a little,

=MAX(INT((IF(OR(E3>$G$17,E3=""),$G$17,E3)-MAX($G$16,D3))/7),1)

markmzz · Sep 27, 2011

Another way (if isn't possible negative results):

Note: I used part of Jasonb75's formula (by the way, good idea).

=MAX(INT((IF(OR(E3>$C$17,E3=""),$C$17,E3)-IF(D3<$C$16,$C$16,D3))/7),1)

Markmzz

jasonb75 · Sep 27, 2011

markmzz said:
Another way (if isn't possible negative results):

Note: I used part of Jasonb75's formula (by the way, good idea).

=MAX(INT((IF(OR(E3>$C$17,E3=""),$C$17,E3)-IF(D3<$C$16,$C$16,D3))/7),1)

Markmzz

Mark, am I missing something?

I can't see any difference between IF(D3<$C$16,$C$16,D3) and MAX(D3,$C$16) other than a possible error if D3 contains a formula blank.

markmzz · Sep 27, 2011

jasonb75 said:
Mark, am I missing something?

I can't see any difference between IF(D3<$C$16,$C$16,D3) and MAX(D3,$C$16) other than a possible error if D3 contains a formula blank.

Jasonb75,

I only used the final part of my first formula and the function MAX of your's formula.

I didn't said that the result of IF(D3<$C$16,$C$16,D3) isn't not equal the result of MAX(D3,$C$16).

Markmzz

Formulas with altenating cells?

yani06

New Member

yani06

New Member

Excel Facts

yani06

New Member

markmzz

MrExcel MVP

yani06

New Member

markmzz

MrExcel MVP

yani06

New Member

jasonb75

Well-known Member

markmzz

MrExcel MVP

jasonb75

Well-known Member

markmzz

MrExcel MVP

Similar threads

Forum statistics

Share this page

Formulas with altenating cells?

New Member

New Member

Excel Facts

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock