|
Complicated IF Formula
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(C61000,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, B11/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(C61000,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:A 10,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 |
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(C61000,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, B11/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(C61000,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:A 10,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 |
Complicated IF Formula
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 |
Complicated IF Formula
<In a nutshell
??? <Make Since!? Not to me, which may be my fault. Try to explain in words what problem you're trying to solve, not what formulas you're trying to get to work. Give examples of input and expected formula results. -- Kind regards, Niek Otten "Luke" wrote in message ... 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(C61000,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, B11/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(C61000,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:A 10,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 |
Complicated IF Formula
Fair enough! Sorry for the delayed response, had to work.:)
First, the formula needs to be in C10 and needs return a 3 digit/or-less number as close as possible to be equal to or less than the largest number in A7:A9 (460) based on C6 (Line) or C6+B5 (B5 added only if B1 date is in the same year) with reference to A7:A9. That is as simple as it gets. To elaborate: "A" columns digits in the array (A7:A9) change "B" column doesn't change (archive if you will) C1 = Today() C6 has its own formula and changes as C1s date changes (plus 1 each day) If B1 is less than 1/1/2005 (in this case it is an earlier year) then the formula in C10 needs to just look at C6 (1165) remove 1000 and display only 165 because it is less than the largest number in the array A7:A9 (460). Think of this as starting to count from one again once you hit 1000. Now, if in B1 the date was, say 7/1/2005 (same year as C1s date), then the formula in C10 would then look at B5 (97) and also C6 (1165) add them together (97+1165), strip off the left €ś1€ť (or 1000 depending on how you see it) and display 262 (97+165) because 262 is less than largest number in the array A7:A9 (460). If B5 was, say 1502 (B1 date 7/1/2005), then C10 would add B5 (1502) and C6 (1165) = 2667 and then remove the left 2 which leaves 667. Now because 667 is greater than the largest number in the array A7:A9 (460), the formula in C10 would then remove the left digit 6 from 667 and display 67 because 67 is less than the largest number in the array A7:A9 (460). Just incase it matters: If or when a Draft shows (dont ask), column C is €śfilled right€ť. I enter the draft number into C2 and enter the number of days it took for the Draft to show in C5. then archive column C and start the whole process over in column D. Thats what I do Here is that portion of the sheet one more time 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 "Niek Otten" wrote: <In a nutshell ??? <Make Since!? Not to me, which may be my fault. Try to explain in words what problem you're trying to solve, not what formulas you're trying to get to work. Give examples of input and expected formula results. -- Kind regards, Niek Otten "Luke" wrote in message ... 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(C61000,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, B11/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(C61000,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:A 10,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 |
Complicated IF Formula
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 |
| All times are GMT +1. The time now is 09:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com