| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Alpha & Numerical Help Needed
"talltom" wrote:
The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alpha & Numerical Help Needed
Ok, let me see if I understand this correctly. You want to test whether the
value in B7 is greater than one of the values in the following three cells, B99, B100 or B101. And these three cells may contain either numbers or the letters "N" and/or "O". See if this is something you can work with: =IF(B7MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""), "O","")+0),True Condition,False Condition) This is an array formula and must be entered with CTRL-SHIFT-ENTER instead of just enter. If done properly, the formula should be enclosed in { }. Note that the SUBSTITUTE formula is case sensitive, so only capital N and capital O will be ignored. HTH, Elkar "talltom" wrote: "talltom" wrote: The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alpha & Numerical Help Needed
Almost, B7 will be a 5 digit Number;ie 52000, or N06000, or O06000.
Depending which number I enter in B7, that will determine the value that will go into B24. I copied your formula into b24 and hit the ctrl-shft-enter keys and it did exactly as you said. Do I have to creat the Array Table? Because B24 is now asking for a #NAME?. Do I change anything in the Formula? "Elkar" wrote: Ok, let me see if I understand this correctly. You want to test whether the value in B7 is greater than one of the values in the following three cells, B99, B100 or B101. And these three cells may contain either numbers or the letters "N" and/or "O". See if this is something you can work with: =IF(B7MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""), "O","")+0),True Condition,False Condition) This is an array formula and must be entered with CTRL-SHIFT-ENTER instead of just enter. If done properly, the formula should be enclosed in { }. Note that the SUBSTITUTE formula is case sensitive, so only capital N and capital O will be ignored. HTH, Elkar "talltom" wrote: "talltom" wrote: The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alpha & Numerical Help Needed
Ok, if B7 may contain letters then you'll need to use SUBSTITUTE on that as
well. =IF(SUBSTITUTE(SUBSTITUTE(B7,"N",""),"O","")+0MIN (SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""),"O","")+0) ,True Condition,False Condition) The array portion of the formula is the B99:B101. This allows the MIN function to apply the SUBSTITUTE function to all three cells without having to type it out 3 seperate times inside of an OR function. The "True Condition" and "False Condition" I just inserted as placeholders. You should place your actual true and false conditions there. If you're getting the #NAME? error, then perhaps something is misspelled, or a quote or paren got lost somewhere? Copy and Paste your exact formula as it appears in cell B24, and we'll see if we can find where the problem is. Elkar "talltom" wrote: Almost, B7 will be a 5 digit Number;ie 52000, or N06000, or O06000. Depending which number I enter in B7, that will determine the value that will go into B24. I copied your formula into b24 and hit the ctrl-shft-enter keys and it did exactly as you said. Do I have to creat the Array Table? Because B24 is now asking for a #NAME?. Do I change anything in the Formula? "Elkar" wrote: Ok, let me see if I understand this correctly. You want to test whether the value in B7 is greater than one of the values in the following three cells, B99, B100 or B101. And these three cells may contain either numbers or the letters "N" and/or "O". See if this is something you can work with: =IF(B7MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""), "O","")+0),True Condition,False Condition) This is an array formula and must be entered with CTRL-SHIFT-ENTER instead of just enter. If done properly, the formula should be enclosed in { }. Note that the SUBSTITUTE formula is case sensitive, so only capital N and capital O will be ignored. HTH, Elkar "talltom" wrote: "talltom" wrote: The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alpha & Numerical Help Needed
The following is the complete working formula if B7B99. B99=52000.
=IF($B$7B$99,$D$93,IF(AND(B$17<=B$100,$F$50,$B$2 0<=6),($F$5*$D$101)+$D$94,IF(AND($B$17$B$100,$B$2 0<=6),($B$17*$B$101*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$101*$F$5*$B$17),D$97)))) =IF(SUBSTITUTE(SUBSTITUTE(B7,"N","06000"),"O","060 00")+0MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N","060 00"),"O","06000")+0),True Condition,False Condition) Do I add the Substitute formula to the existing formula? Do I add the 5 digit numbers to the "" you show? What should I put in the "TrueCondition,False Condition" spots? talltom "Elkar" wrote: Ok, if B7 may contain letters then you'll need to use SUBSTITUTE on that as well. =IF(SUBSTITUTE(SUBSTITUTE(B7,"N",""),"O","")+0MIN (SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""),"O","")+0) ,True Condition,False Condition) The array portion of the formula is the B99:B101. This allows the MIN function to apply the SUBSTITUTE function to all three cells without having to type it out 3 seperate times inside of an OR function. The "True Condition" and "False Condition" I just inserted as placeholders. You should place your actual true and false conditions there. If you're getting the #NAME? error, then perhaps something is misspelled, or a quote or paren got lost somewhere? Copy and Paste your exact formula as it appears in cell B24, and we'll see if we can find where the problem is. Elkar "talltom" wrote: Almost, B7 will be a 5 digit Number;ie 52000, or N06000, or O06000. Depending which number I enter in B7, that will determine the value that will go into B24. I copied your formula into b24 and hit the ctrl-shft-enter keys and it did exactly as you said. Do I have to creat the Array Table? Because B24 is now asking for a #NAME?. Do I change anything in the Formula? "Elkar" wrote: Ok, let me see if I understand this correctly. You want to test whether the value in B7 is greater than one of the values in the following three cells, B99, B100 or B101. And these three cells may contain either numbers or the letters "N" and/or "O". See if this is something you can work with: =IF(B7MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""), "O","")+0),True Condition,False Condition) This is an array formula and must be entered with CTRL-SHIFT-ENTER instead of just enter. If done properly, the formula should be enclosed in { }. Note that the SUBSTITUTE formula is case sensitive, so only capital N and capital O will be ignored. HTH, Elkar "talltom" wrote: "talltom" wrote: The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alpha & Numerical Help Needed
Ok, combining my formula with your original, it should look like this:
=IF(SUBSTITUTE(SUBSTITUTE($B$7,"N",""),"O","")+0M IN(SUBSTITUTE(SUBSTITUTE($B$99:$B$101,"N",""),"O", "")+0),$D$93,IF(AND(B$17<=B$100,$F$50,$B$20<=6),( $F$5*$D$101)+$D$94,IF(AND($B$17$B$100,$B$20<=6),( $B$17*$B$101*$F$5)+$D$95,IF($B$206,($F$5*$D$96)+$ D$94+($B$101*$F$5*$B$17),D$97)))) Remember, this is an array formula, so use CTRL-SHIFT-ENTER. HTH, Elkar "talltom" wrote: The following is the complete working formula if B7B99. B99=52000. =IF($B$7B$99,$D$93,IF(AND(B$17<=B$100,$F$50,$B$2 0<=6),($F$5*$D$101)+$D$94,IF(AND($B$17$B$100,$B$2 0<=6),($B$17*$B$101*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$101*$F$5*$B$17),D$97)))) =IF(SUBSTITUTE(SUBSTITUTE(B7,"N","06000"),"O","060 00")+0MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N","060 00"),"O","06000")+0),True Condition,False Condition) Do I add the Substitute formula to the existing formula? Do I add the 5 digit numbers to the "" you show? What should I put in the "TrueCondition,False Condition" spots? talltom "Elkar" wrote: Ok, if B7 may contain letters then you'll need to use SUBSTITUTE on that as well. =IF(SUBSTITUTE(SUBSTITUTE(B7,"N",""),"O","")+0MIN (SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""),"O","")+0) ,True Condition,False Condition) The array portion of the formula is the B99:B101. This allows the MIN function to apply the SUBSTITUTE function to all three cells without having to type it out 3 seperate times inside of an OR function. The "True Condition" and "False Condition" I just inserted as placeholders. You should place your actual true and false conditions there. If you're getting the #NAME? error, then perhaps something is misspelled, or a quote or paren got lost somewhere? Copy and Paste your exact formula as it appears in cell B24, and we'll see if we can find where the problem is. Elkar "talltom" wrote: Almost, B7 will be a 5 digit Number;ie 52000, or N06000, or O06000. Depending which number I enter in B7, that will determine the value that will go into B24. I copied your formula into b24 and hit the ctrl-shft-enter keys and it did exactly as you said. Do I have to creat the Array Table? Because B24 is now asking for a #NAME?. Do I change anything in the Formula? "Elkar" wrote: Ok, let me see if I understand this correctly. You want to test whether the value in B7 is greater than one of the values in the following three cells, B99, B100 or B101. And these three cells may contain either numbers or the letters "N" and/or "O". See if this is something you can work with: =IF(B7MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""), "O","")+0),True Condition,False Condition) This is an array formula and must be entered with CTRL-SHIFT-ENTER instead of just enter. If done properly, the formula should be enclosed in { }. Note that the SUBSTITUTE formula is case sensitive, so only capital N and capital O will be ignored. HTH, Elkar "talltom" wrote: "talltom" wrote: The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Alpha & Numerical Help Needed
We're doing great. The formula works for the "N" #, and the "O" #, but not
the 52000 #. Also the "N" # and "O" # works in reverse. ie the formula gives me a higher amount for the 06000 than for 06001 or higher. I would like the formula to return the higher # when I put in a higher # in B7 talltom "Elkar" wrote: Ok, combining my formula with your original, it should look like this: =IF(SUBSTITUTE(SUBSTITUTE($B$7,"N",""),"O","")+0M IN(SUBSTITUTE(SUBSTITUTE($B$99:$B$101,"N",""),"O", "")+0),$D$93,IF(AND(B$17<=B$100,$F$50,$B$20<=6),( $F$5*$D$101)+$D$94,IF(AND($B$17$B$100,$B$20<=6),( $B$17*$B$101*$F$5)+$D$95,IF($B$206,($F$5*$D$96)+$ D$94+($B$101*$F$5*$B$17),D$97)))) Remember, this is an array formula, so use CTRL-SHIFT-ENTER. HTH, Elkar "talltom" wrote: The following is the complete working formula if B7B99. B99=52000. =IF($B$7B$99,$D$93,IF(AND(B$17<=B$100,$F$50,$B$2 0<=6),($F$5*$D$101)+$D$94,IF(AND($B$17$B$100,$B$2 0<=6),($B$17*$B$101*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$101*$F$5*$B$17),D$97)))) =IF(SUBSTITUTE(SUBSTITUTE(B7,"N","06000"),"O","060 00")+0MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N","060 00"),"O","06000")+0),True Condition,False Condition) Do I add the Substitute formula to the existing formula? Do I add the 5 digit numbers to the "" you show? What should I put in the "TrueCondition,False Condition" spots? talltom "Elkar" wrote: Ok, if B7 may contain letters then you'll need to use SUBSTITUTE on that as well. =IF(SUBSTITUTE(SUBSTITUTE(B7,"N",""),"O","")+0MIN (SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""),"O","")+0) ,True Condition,False Condition) The array portion of the formula is the B99:B101. This allows the MIN function to apply the SUBSTITUTE function to all three cells without having to type it out 3 seperate times inside of an OR function. The "True Condition" and "False Condition" I just inserted as placeholders. You should place your actual true and false conditions there. If you're getting the #NAME? error, then perhaps something is misspelled, or a quote or paren got lost somewhere? Copy and Paste your exact formula as it appears in cell B24, and we'll see if we can find where the problem is. Elkar "talltom" wrote: Almost, B7 will be a 5 digit Number;ie 52000, or N06000, or O06000. Depending which number I enter in B7, that will determine the value that will go into B24. I copied your formula into b24 and hit the ctrl-shft-enter keys and it did exactly as you said. Do I have to creat the Array Table? Because B24 is now asking for a #NAME?. Do I change anything in the Formula? "Elkar" wrote: Ok, let me see if I understand this correctly. You want to test whether the value in B7 is greater than one of the values in the following three cells, B99, B100 or B101. And these three cells may contain either numbers or the letters "N" and/or "O". See if this is something you can work with: =IF(B7MIN(SUBSTITUTE(SUBSTITUTE(B99:B101,"N",""), "O","")+0),True Condition,False Condition) This is an array formula and must be entered with CTRL-SHIFT-ENTER instead of just enter. If done properly, the formula should be enclosed in { }. Note that the SUBSTITUTE formula is case sensitive, so only capital N and capital O will be ignored. HTH, Elkar "talltom" wrote: "talltom" wrote: The following formula is working as it should, but now I need this formula to recognize work order numbers that have either a 5 digit number, or an "N" preceeding or trailing the 5 digit number, or an "O" preceeding or trailing the 5 digit number. The formula works fine with just numbers.ie (B$99=52000.) =IF($B$7B$99,$D$93,IF(AND(B$17<=B$102,$F$50,$B$2 0<=6),($F$5*$D$103)+$D$94,IF(AND($B$17$B$102,$B$2 0<=6),($B$17*$B$103*$F$5)+$D$95,IF($B$206,($F$5*$ D$96)+$D$94+($B$103*$F$5*$B$17))))) B$99=52000, B$100=N06000, and B$101=O06000 This is what I've tried with no success. =IF(OR($B$7B$100,$B$7B$101,$B$7B$99,$D$93...... ... We need the formula to give us an answer based on the number sequence I put into B7. Thank you in advance!! The following was sent to me by teethless mama, but I cant get it to work. =IF(OR(B7RIGHT(B100,LEN(B100)-1)+0,B7RIGHT(B101,LEN(B101)-1)+0),"your formula","") Adjust to suit your needs |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| if function checking for numerical data vs alpha characters | Excel Worksheet Functions | |||
| how do you change excel columns from numerical to alpha | Excel Discussion (Misc queries) | |||
| alpha column changed to numerical column | New Users to Excel | |||
| Numerical grade to Alpha character | Excel Discussion (Misc queries) | |||
| way to automaitic sort sheet one numerical, sheet two alpha? | Excel Worksheet Functions | |||
Linear Mode
