Home |
Search |
Today's Posts |
#1
|
|||
|
|||
2nd attempt ~ complicated formula
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 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 Luke |
#2
|
|||
|
|||
2nd attempt ~ complicated formula
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. Question1: Why is 165 displayed? Because the remainder of C6/1000 is < the largest number in the array? What happens when it's than the largest # in the array? Now, if in B1 the date was, say 7/1/2005 (same year as C1's 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). Question 2: What happens when the sum of B5 and C6 are greater than the largest # in the array? 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). Question 3: What happens when the remainder of (B5+C6) is < the largest # in the array. There are pieces of this puzzle still missing. "Luke" wrote in message ... 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" column's 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 C1's 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 C1's 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 (don't 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. That's what I do Here is that portion of the sheet 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 Luke |
#3
|
|||
|
|||
2nd attempt ~ complicated formula
Try in C10
=IF(MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000)<MAX($ A$7:$A$9),MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000) ,MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),100)) This assumes that when you add new columns, the comparison is always against the column immediately to the left except for A7:A9 which is an "absolute" range and all comparisons are made against that, that is, column C compares against column B, column B against column C, etc., except that they always compare against the maximum of A7:A9. The formula could be shortened considerably if you added another row to contain =MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000) which occurs twice in the above formula. If you entered that formula in C11, then the C10 formula would be =IF(C11<MAX($A$8:$A$9),C11,MOD(C11,100)) I think these formulas reflect the logic you outlined. Let us know if they do or don't. HTH Declan O'R |
#4
|
|||
|
|||
2nd attempt ~ complicated formula
DOR YOU ARE ABSOLUTELY HUGE!
I have been searching and trying differnt things for months... you guys are always my last resort and you never cease to come through. Thank you DOR for actually reading the "word problem", understanding and solving not only what I was looking for but you also had the foresight to include the ability to archive the current data and move it into the next level. Outstanding time frame!!! I can't say enough good about you guys. You ARE the Best. PS I will try that two row formula for speed because I have a very large worksheet so I am sure it will help. Luke "DOR" wrote: Try in C10 =IF(MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000)<MAX($ A$7:$A$9),MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000) ,MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),100)) This assumes that when you add new columns, the comparison is always against the column immediately to the left except for A7:A9 which is an "absolute" range and all comparisons are made against that, that is, column C compares against column B, column B against column C, etc., except that they always compare against the maximum of A7:A9. The formula could be shortened considerably if you added another row to contain =MOD(C6+IF(YEAR(B1)=YEAR(C1),B5,0),1000) which occurs twice in the above formula. If you entered that formula in C11, then the C10 formula would be =IF(C11<MAX($A$8:$A$9),C11,MOD(C11,100)) I think these formulas reflect the logic you outlined. Let us know if they do or don't. HTH Declan O'R |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated IF Formula | Excel Worksheet Functions | |||
Creating a check mark box | Setting up and Configuration of Excel | |||
Replacing MCONCAT with CONCAT_RANGE (in a complicated formula) | Excel Worksheet Functions | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |