Complicated IF Formula

Is there anyone looking at this?

"Luke" wrote:

> The following samples are from individual sections of my woorksheet.
> Save Human Error.
> C10 is where my fourmula is at.
> Here is part of the equation
> C10 says that if B1 is less than 1/1/2005 then look at C6.
> IF(AND(C6>1000,RIGHT(C6,3)<=large(A7:A10,1)) then display RIGHT(C6,3),
> otherwise if C6<1000 then C6.
>
> In Sample1 B1 is less than 1/1/2005, but in Sample2, B1>1/1/2005
>
> Sample 1
> A B C
> 1 date 8/29/2002 11/6/2005
> 2 draft 40
> 3 since 97 1165
> 4 first
> 5 days 97
> 6 line 13521 1165
> 7 4 3458
> 8 410 6798 1165
> 9 460 5745
> 10 Chec 165
>
> In Sample2, C10 says is the same as in sample1 but because B1>=1/1/2005, B5
> becomes a factor.
>
> Sample 2
> 1 date 7/1/2005 11/6/2005
> 2 draft 700
> 3 since 178 128
> 4 first
> 5 days 1502
> 6 line 21937 128
> 7 71 306
> 8 107 1518
> 9 700 7835 128
> 10 Chec 630
> Therefore
> C10 says that if B1<=1/1/2005 then look at C6.
> IF(AND(C6>1000,RIGHT(C6,3)<=large(A7:A10,1)) then display RIGHT(C6,3),
> otherwise if C6<1000 then C6
> Otherwise,
> IF(B1>=1/1/2005, then look at B5 & C6.
> IF(AND(B5>=1000,RIGHT(B5+C6,3)<=large(A7:A10,1)) then RIGHT(B5+C6,3)
> otherwise if(AND(B5<=1000,C6<1000,RIGHT(B5+C6,3)<=large(A7:A10,1)) then
> RIGHT(B5+C6,3),B5+C6<1000,B5+C6
>
> In a nutshell
> Essentially, weather B5 & C6 are together or if we are just looking at C6 ,
> the ultimate return in C10 should be as close as possible to a 3 digit number
> that is equal to or less than large(A7:A10,1)
> It can eeven go as far as RIGHT(C6,2) just to get it within large(A7:A10,1)
> Make Since!?
> Luke

Luke,
I am not really sure what you are looking for here, but the one thing
that is certainly not going to work in your formulas is comparison of
text and numbers. Use VALUE(RIGHT(...))<=whatever if you want to
compare the numeric value of a portion of text with another cell that
has numeric content.

HTH
Kostis Vezerides

Yes I understand and I do use it but perhaps I was not clear enough. see my
response post to Niek Otten.
Luke

"vezerid" wrote:

> Luke,
> I am not really sure what you are looking for here, but the one thing
> that is certainly not going to work in your formulas is comparison of
> text and numbers. Use VALUE(RIGHT(...))<=whatever if you want to
> compare the numeric value of a portion of text with another cell that
> has numeric content.
>
> HTH
> Kostis Vezerides
>
>