If Formula name equals X, then value is

C

Calop

I get that Deer in the headlight look, just thinking about this.
I need a formula if it is feasible: 1.0 Mbs through 1.5 Mbs = 1.5M, 5.0 Mbs
through 100.0 Mbs = 100M, 101.0 Mbs and higher = current value minus the .
space. Just the M after last number. Exceptions are: 45.0 Mbs = 45M, 300.0
Mbs = 300M, 384.0 Kbs = 384K.
example:
1.5 Mbs 1.5M
12.0 Mbs 100M
100.0 Mbs 100M
112.0 Mbs 112M
300.0 Mbs 300M
21.0 Mbs 100M
18.0 Mbs 100M
384.0 Kbs 384K
45.0 Mbs 45M
148.0 Mbs 148M

Thank you, Calop
 
B

Bob Phillips

=IF(A1="45 Mbs","45M",IF(A1="300 Mbs","300M",IF(A1="384
Mbs","384M",IF(--LEFT(A1,FIND(" ",A1))<=1.5,"1.5M",IF(A1<="100
Mbs""100M",LEFT(A1,FIND(" ",A1))&"M")))))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
T

tcebob

I'm surprised that excel does not have some sort of Condition function. Like

=Condition(A1="45 Mbs","45M",A1="300 Mbs","300M", . . . etc.

which is common in most programming languages. The nested ifs are ok logically but a pain
to read and edit.

Maybe some function package?

rs

: =IF(A1="45 Mbs","45M",IF(A1="300 Mbs","300M",IF(A1="384
: Mbs","384M",IF(--LEFT(A1,FIND(" ",A1))<=1.5,"1.5M",IF(A1<="100
: Mbs""100M",LEFT(A1,FIND(" ",A1))&"M")))))
:
: --
: HTH
:
: Bob Phillips
:
: (replace somewhere in email address with gmail if mailing direct)
:
: : > I get that Deer in the headlight look, just thinking about this.
: > I need a formula if it is feasible: 1.0 Mbs through 1.5 Mbs = 1.5M, 5.0
: Mbs
: > through 100.0 Mbs = 100M, 101.0 Mbs and higher = current value minus the .
: > space. Just the M after last number. Exceptions are: 45.0 Mbs = 45M,
: 300.0
: > Mbs = 300M, 384.0 Kbs = 384K.
: > example:
: > 1.5 Mbs 1.5M
: > 12.0 Mbs 100M
: > 100.0 Mbs 100M
: > 112.0 Mbs 112M
: > 300.0 Mbs 300M
: > 21.0 Mbs 100M
: > 18.0 Mbs 100M
: > 384.0 Kbs 384K
: > 45.0 Mbs 45M
: > 148.0 Mbs 148M
: >
: > Thank you, Calop
: >
: >
:
:
 
D

David F Cox

As with most thing in life, you need to get rid of the bs :->.

I think this is one way:

IIf(IsNull([myfield]),"",Left([myfield],Len([myfield])-2))

and I think this another, testing for "bs"

IIf(Len([myfield])>2 And
Right([myfield],2)="bs",Left([myfield],Len([myfield])-2),[myfield])
 
N

Niek Otten

For a very elegant solution, look at the VLOOKUP() function.

Here's a tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm surprised that excel does not have some sort of Condition function. Like
|
| =Condition(A1="45 Mbs","45M",A1="300 Mbs","300M", . . . etc.
|
| which is common in most programming languages. The nested ifs are ok logically but a pain
| to read and edit.
|
| Maybe some function package?
|
| rs
|
| | : =IF(A1="45 Mbs","45M",IF(A1="300 Mbs","300M",IF(A1="384
| : Mbs","384M",IF(--LEFT(A1,FIND(" ",A1))<=1.5,"1.5M",IF(A1<="100
| : Mbs""100M",LEFT(A1,FIND(" ",A1))&"M")))))
| :
| : --
| : HTH
| :
| : Bob Phillips
| :
| : (replace somewhere in email address with gmail if mailing direct)
| :
| : | : > I get that Deer in the headlight look, just thinking about this.
| : > I need a formula if it is feasible: 1.0 Mbs through 1.5 Mbs = 1.5M, 5.0
| : Mbs
| : > through 100.0 Mbs = 100M, 101.0 Mbs and higher = current value minus the .
| : > space. Just the M after last number. Exceptions are: 45.0 Mbs = 45M,
| : 300.0
| : > Mbs = 300M, 384.0 Kbs = 384K.
| : > example:
| : > 1.5 Mbs 1.5M
| : > 12.0 Mbs 100M
| : > 100.0 Mbs 100M
| : > 112.0 Mbs 112M
| : > 300.0 Mbs 300M
| : > 21.0 Mbs 100M
| : > 18.0 Mbs 100M
| : > 384.0 Kbs 384K
| : > 45.0 Mbs 45M
| : > 148.0 Mbs 148M
| : >
| : > Thank you, Calop
| : >
| : >
| :
| :
|
|
 
Top