Here you are,
=IF(C5=0,J6,IF(RIGHT(C5,1)
="+",IF(OR(J6=0,J6=""),0,IF(J6>INT(MID(C5,1,LEN(C5)-1)),J6,INT(MID(C5,1,LEN(C5)-1)))),ROUNDUP(J6/INT(MID(C5,1,LEN(C5))),0)*INT(MID(C5,1,LEN(C5)))))
That should do it!
Cheers,
Jason Lepack
DtTall wrote:
> Hi Jason,
>
> Thanks for your help. One thing, when using the 100+ I should have
> clarified that if there are no requirements in J6, J7 should be zero
> and when there are requirement in J6, then the "minimum 100 but
> anything over that is ok" should apply. I tried to tease out how to
> fix that, but to no avail. Could you help me out once more?
>
> Thanks,
>
> David Schuette
> (DtTall)
>
>
> On Nov 16, 1:52 pm, "jlepack" <jlep...@gmail.com> wrote:
> > PS.
> >
> > I assumed that there would always be:
> > a) a zero (instead of LFL)
> > b) a number followed by a plus sign
> > c) a regular number
> >
> > This you can put any number you want before the "+" and it will
> > calculate correctly.
> > J6 = 205 or 9
> > C5 = "10+"
> > J7 = 205 or 10
> >
> > Cheers,
> > Jason Lepack
> >
> > jlepack wrote:
> > > Here's the formula for J7.
> >
> > > =IF(C5=0,J6,IF(RIGHT(C5,1) =
> > > "+",IF(J6>INT(MID(C5,1,LEN(C5)-1)),J6,INT(MID(C5,1,LEN(C5)-1))),ROUNDUP(J6/ INT(MID(C5,1,LEN(C5))),0)*INT(MID(C5,1,LEN(C5)))))
> >
> > > Cheers,
> > > Jason Lepack
> >
> > > DtTall wrote:
> > > > This is a weird one. I don't even know if something like this is
> > > > possible. That said, here is my issue.
> >
> > > > I have cell J6=205. Now, I want J7 to change based on information in
> > > > cell C5.
> >
> > > > Here is what I am trying to do.
> > > > -The user can enter "LFL" (lot-for-lot) in C5 and then J7=J6. (or some
> > > > number, say 0, could be used in place of LFL I guess)
> > > > -The user could also enter some integer, say "100" in C5 and then J7
> > > > can only be multiples of 100. To cover the 205 in J6, it would need
> > > > 300 then in J7.
> > > > -The Final thing they could enter is something like "100+" in C5 in
> > > > which case the minimum vlaue of J7 is 100, but can be anything over
> > > > that, so 205 would end up in J7.
> >
> > > > Is there any possible way to do this? I know it is a tough problem,
> > > > but any help would be great.
> >
> > > > Thanks,
> >
> > > > DtTall
Similar Threads
