If Formula name equals X, then value is

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Calop - 14 Oct 2006 18:26 GMT

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

Reply to this Message

Bob Phillips - 14 Oct 2006 19:05 GMT

=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")))))

Signature

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
[quoted text clipped - 14 lines]
>
> Thank you, Calop

Reply to this Message

tcebob - 14 Oct 2006 19:37 GMT

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
[quoted text clipped - 20 lines]
: >
: > Thank you, Calop

Reply to this Message

Niek Otten - 14 Oct 2006 20:27 GMT

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

Here's a tutorial:

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| I'm surprised that excel does not have some sort of Condition function. Like
|
[quoted text clipped - 31 lines]
| : >
| : > Thank you, Calop

Reply to this Message

David F Cox - 14 Oct 2006 20:02 GMT

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])

>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
[quoted text clipped - 14 lines]
>
> Thank you, Calop

Reply to this Message

David F Cox - 14 Oct 2006 20:04 GMT

and you need to know when you are in an Excel group and not an Access one.

Sorry all. :-<

"David F Cox" <nospam@please.com> wrote in message news:...

> As with most thing in life, you need to get rid of the bs :->.
>
[quoted text clipped - 25 lines]
>>
>> Thank you, Calop

Reply to this Message