Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
Hello,
I need your expertise. I have few tabs with the similar data, where (a) is below scope. I want to order all the variances above the scope (a) in consecutive numbers by ignoring the blanks and below scope items. I am using this formula but it doesn't work because it is counting a's. =IF((ABS(C6)250)+ABS(E6)10%,COUNT($C$6:C6)&"","{ a}") In addition, I want to use the next available number in the following tab, so if the last item in sheet1 is 250, the next item in sheet2 would be 251. Is this possible? Thanks in advance, a scope <2 & .25% 2006 2005 Change % A B C D E 10 8 2 0.25 {a} 15 6 9 1.5 2 8 9 -1 -0.111111111 {a} 9 10 -1 -0.1 {a} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
One interp on your main query and a way to get there ..
Assuming data in cols A to E, from row6 down Cols C (change) and E (scope) are the key cols Assuming empty cols to the right of col E, paste the same col headers from A5:E5 into G5:K5 Then put in F6: =IF(OR(E6="",E6="{a}"),"",C6+ROW()/10^10) (Leave F1:F5 blank) Put in G6: =IF(ROW(A1)COUNT($F:$F),"",INDEX(A:A,MATCH(SMALL( $F:$F,ROW(A1)),$F:$F,0))) Copy G6 across by 5 cols to K6. Then select F6:K6, fill down to cover the max expected extent of source data. Hide away col F. Cols G to K will return only the lines from cols A to E which are not either blank or contain {a} under col E (scope), with all lines neatly auto-sorted at the top in ascending order by the values in col C (change). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lisa" wrote: Hello, I need your expertise. I have few tabs with the similar data, where (a) is below scope. I want to order all the variances above the scope (a) in consecutive numbers by ignoring the blanks and below scope items. I am using this formula but it doesn't work because it is counting a's. =IF((ABS(C6)250)+ABS(E6)10%,COUNT($C$6:C6)&"","{ a}") In addition, I want to use the next available number in the following tab, so if the last item in sheet1 is 250, the next item in sheet2 would be 251. Is this possible? Thanks in advance, a scope <2 & .25% 2006 2005 Change % A B C D E 10 8 2 0.25 {a} 15 6 9 1.5 2 8 9 -1 -0.111111111 {a} 9 10 -1 -0.1 {a} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
"Lisa" wrote:
.... In addition, I want to use the next available number in the following tab, so if the last item in sheet1 is 250, the next item in sheet2 would be 251. Is this possible? Some thoughts to address your 2nd query above .. Assume that in Sheet1, you have row numbering applied in col A from A6 down, using for eg in A6: =ROW(A1) with A6 copied down. Then in Sheet2, you could place in the corresponding start cell A6: =MAX(Sheet1!$A$6:$A$65536)+ROW(A1) and copy A6 down. Sheet2 will return the consecutive numbering linked to Sheet1 that's wanted. If you're certain that there's going to be no numbers (or dates) placed within A1:A5, just use entire cols instead, viz in Sheet2's A6, copied down: =MAX(Sheet1!A:A)+ROW(A1) Extend accordingly for the other sheets .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
Hi Max,
Thank you for the prompt response. I think I wasn't clear enough. On column E I do want to show (a) below scope, but I wanted the above scope items to be in sequencial order:for example Current Sit Desired Col E Col E 1. a a 2. 2 1 3. a a 4. a a 5. 5 2 The second formula works prefectly when I apply as in your example, but for some reason when I apply to my spreadsheet returns wrong values.... I would like to thank you once again for taking time to assist me... "Max" wrote: "Lisa" wrote: ... In addition, I want to use the next available number in the following tab, so if the last item in sheet1 is 250, the next item in sheet2 would be 251. Is this possible? Some thoughts to address your 2nd query above .. Assume that in Sheet1, you have row numbering applied in col A from A6 down, using for eg in A6: =ROW(A1) with A6 copied down. Then in Sheet2, you could place in the corresponding start cell A6: =MAX(Sheet1!$A$6:$A$65536)+ROW(A1) and copy A6 down. Sheet2 will return the consecutive numbering linked to Sheet1 that's wanted. If you're certain that there's going to be no numbers (or dates) placed within A1:A5, just use entire cols instead, viz in Sheet2's A6, copied down: =MAX(Sheet1!A:A)+ROW(A1) Extend accordingly for the other sheets .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
On your main query, afraid I'm not able to offer a solution to get it sorted
in exactly the manner you illustrated below. But if you'd accept an alternative "close-fit" sort where "numbers" would appear in ascending sort order above the lines with {a}'s, viz.: Col E -- In col K 1. a 1 2. 2 2 3. a a 4. a a 5. 5 a then we could use this criteria instead in F6, with F6 copied down: =IF(E6="","",IF(E6="{a}",ROW()+10^10,C6+ROW()/10^10)) (no change to the formulas in cols G to K) As for your linked numbering query: .. The second formula works perfectly when I apply as in your example, but for some reason when I apply to my spreadsheet returns wrong values.... If it's not working in Sheet2, check that all the numbers used in Sheet1's numbering (the first sheet) are real numbers, not text numbers. Text numbers, if any, would be ignored by MAX(..). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lisa" wrote in message ... Hi Max, Thank you for the prompt response. I think I wasn't clear enough. On column E I do want to show (a) below scope, but I wanted the above scope items to be in sequencial order:for example Current Sit Desired Col E Col E 1. a a 2. 2 1 3. a a 4. a a 5. 5 2 The second formula works prefectly when I apply as in your example, but for some reason when I apply to my spreadsheet returns wrong values.... I would like to thank you once again for taking time to assist me... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
Unfortunately, I cannot change the row order since it is for financial
statements. Thanks anyways... "Max" wrote: On your main query, afraid I'm not able to offer a solution to get it sorted in exactly the manner you illustrated below. But if you'd accept an alternative "close-fit" sort where "numbers" would appear in ascending sort order above the lines with {a}'s, viz.: Col E -- In col K 1. a 1 2. 2 2 3. a a 4. a a 5. 5 a then we could use this criteria instead in F6, with F6 copied down: =IF(E6="","",IF(E6="{a}",ROW()+10^10,C6+ROW()/10^10)) (no change to the formulas in cols G to K) As for your linked numbering query: .. The second formula works perfectly when I apply as in your example, but for some reason when I apply to my spreadsheet returns wrong values.... If it's not working in Sheet2, check that all the numbers used in Sheet1's numbering (the first sheet) are real numbers, not text numbers. Text numbers, if any, would be ignored by MAX(..). -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lisa" wrote in message ... Hi Max, Thank you for the prompt response. I think I wasn't clear enough. On column E I do want to show (a) below scope, but I wanted the above scope items to be in sequencial order:for example Current Sit Desired Col E Col E 1. a a 2. 2 1 3. a a 4. a a 5. 5 2 The second formula works prefectly when I apply as in your example, but for some reason when I apply to my spreadsheet returns wrong values.... I would like to thank you once again for taking time to assist me... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
No prob. Thanks for feeding back. Monitor your thread awhile. Perhaps there
could be insights for you from other responders. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lisa" wrote in message ... Unfortunately, I cannot change the row order since it is for financial statements. Thanks anyways... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
In your condition test you are adding a logical (ABS(C6)250), which will
have a value of 0 or 1, to ABS(E6), and then comparing the total with 10%. Is that what you're trying to do? In other words you'll get the TRUE condition either if ABS(E6) is greater than 10%, or if ABS(C6)250 [because in the latter case it doesn't matter what ABS() value we add to the logical 1 result, it will already be above the 10% threshold.] If that's what you're trying to do, it might be clearer if you express it as an OR. -- David Biddulph "Lisa" wrote in message ... Hello, I need your expertise. I have few tabs with the similar data, where (a) is below scope. I want to order all the variances above the scope (a) in consecutive numbers by ignoring the blanks and below scope items. I am using this formula but it doesn't work because it is counting a's. =IF((ABS(C6)250)+ABS(E6)10%,COUNT($C$6:C6)&"","{ a}") .... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Assign auto numbers based on a condition
Thank you, guys!
I think I found a solution, not perfect but it seems that it works. =IF((ABS(C6)250)+ABS(D6)10%,COUNT($E$5:E5,1),"{a }") "David Biddulph" wrote: In your condition test you are adding a logical (ABS(C6)250), which will have a value of 0 or 1, to ABS(E6), and then comparing the total with 10%. Is that what you're trying to do? In other words you'll get the TRUE condition either if ABS(E6) is greater than 10%, or if ABS(C6)250 [because in the latter case it doesn't matter what ABS() value we add to the logical 1 result, it will already be above the 10% threshold.] If that's what you're trying to do, it might be clearer if you express it as an OR. -- David Biddulph "Lisa" wrote in message ... Hello, I need your expertise. I have few tabs with the similar data, where (a) is below scope. I want to order all the variances above the scope (a) in consecutive numbers by ignoring the blanks and below scope items. I am using this formula but it doesn't work because it is counting a's. =IF((ABS(C6)250)+ABS(E6)10%,COUNT($C$6:C6)&"","{ a}") .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
scrolling numbers in auto filter? | Excel Discussion (Misc queries) | |||
Assign number to groups of same numbers | Excel Discussion (Misc queries) | |||
Excel auto formats cells with numbers - Can it be disabled? | Setting up and Configuration of Excel | |||
Assign territories to Agents based on Zipcode lead history | Excel Discussion (Misc queries) | |||
assign auto number and auto date | Excel Discussion (Misc queries) |