IF statement Problem

  • Thread starter Thread starter prpaster
  • Start date Start date
P

prpaster

Refer to the file below.. Now the problem that i am having is that
have everything almost working all i have to do is limit so that if th
number under MOS which is 60 in this case. So therefore the number
under the years cant add up to over 60 and when they do i need to t
display a 0


THanks

Pau

Attachment filename: if statements.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63105
 
Paul,

I'll pass on the attachment: they are rarely necessary and always dangerous.

Let's say that you have some formula:

=SUM(blah blah)

Change it to:

=IF(SUM(blah blah)>60,0,SUM(blah blah))

HTH,
Bernie
MS Excel MVP
 
Well thanks but i wish u did look at the formula that is in th
spreadsheet. This formula already is super long and i cant figure ou
where to impose this addition to the fomul
 
That's the problem! No sane person opens up a file from a stranger just like
that.
It's better you describe it in a thorough manner

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Well thanks but i wish u did look at the formula that is in the
spreadsheet. This formula already is super long and i cant figure out
where to impose this addition to the fomula
Click to expand...

Most people will follow Bernie's lead on this one. If you want the
formula looked at post it in the message body.

Dave
 
Nah, not gonna.

Anyway, if it's a super long formula, and in cell A1, then use

=IF(A1>60,0,A1)

HTH,
Bernie
MS Excel MVP
 
ok here it is
this is what the spreadsheet looks like

SOP MOS cum 2004 2005 2005 2007 2008 2009 2010


Under Start of Production=10/1/2004
under Months of Service=60
Under CUMM=6,000,000
Under 2004=IF(A2<DATE(2005,1,1),(DATE(2005,1,1)-A2)/30.4)
Under 2005=IF(A2<DATE(2006,1,1), IF((DATE(2006,1,1)-A2)/30.4-D2>12
12,(DATE(2006,1,1)-A2)/30.4),0)
Unde
2006=IF(A2<DATE(2007,1,1),IF((DATE(2007,1,1)-A2)/30.4-D2-E2>12,12,IF(B2<D2+E2,0,(DATE(2007,1,1)-A2)/30.4)))
Under 2007=IF(A2<DATE(2008,1,1)
IF((DATE(2008,1,1)-A2)/30.4-D2-E2-F2>12
12,(DATE(2008,1,1)-A2)/30.4),0)
Under 2008=IF(A2<DATE(2009,1,1)
IF((DATE(2009,1,1)-A2)/30.4-D2-E2-F2-G2>12
12,(DATE(2009,1,1)-A2)/30.4),0)
Under 2009=IF(A2<DATE(2010,1,1)
IF((((DATE(2010,1,1)-A2)/30.4)-D2-E2-F2-G2-H2-D2)>12
12,(DATE(2010,1,1)-A2)/30.4)-D2-E2-F2-G2-H2-D2,0)



The Objects of this Formula was to do the following

Reconize when the product is started and state the months for each yea
that it is produced. it should show 3 in 2004,12 in 2005, 12 in 2006, 1
in 2007 , 12 in 2008 and 9 in 2009 which equals the amount of month i
service


Thanks

Pau
 
prpaster wrote...
...
SOP MOS cum 2004 2005 2005 2007 2008 2009 2010
Click to expand...

Presumably this is A1:J1.
Under Start of Production=10/1/2004
Click to expand...

Meaning A2 contains the date constant 10/1/2004?
under Months of Service=60
Click to expand...

Meaning B2 contains the number constant 60?
Under CUMM=6,000,000
Click to expand...

Meaning C2 contains the number constant 6000000?
Under 2004
=IF(A2<DATE(2005,1,1),(DATE(2005,1,1)-A2)/30.4)
Click to expand...

Meaning this formula is in D2? If so, and if the answers to th
preceding questions were all yes, rewrite this as

=MIN(B2,MAX(0,DATE(D$1+1,1,1)-A2)/30.4)
Under 2005
=IF(A2<DATE(2006,1,1), IF((DATE(2006,1,1)-A2)/30.4-D2>12,
12,(DATE(2006,1,1)-A2)/30.4),0)
Click to expand...
...

Meaning this formula is in E2? If so etc., rewrite this as

=MAX(0,MIN(12,MIN($B2,(MAX(0,DATE(E$1+1,1,1)-$A2))/30.4)
-SUM($D2:D2)))

Then fill E2 right into F2:J2. These formulas can return numbers wit
fractional parts. Adding rounding or truncation is left to you as a
exercise
 
prpaster wrote...
...
this is what the spreadsheet looks like

SOP MOS cum 2004 2005 2005 2007 2008 2009 2010
Click to expand...
...

Forgot to mention that you have two instances of 2005. Presumably th
second one should be 2006
 
Back
Top