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