=IF(k2<1;a;IF(k2<2;b;IF(k2<3;c;IF(k2<4;d;IF(k2<5;e;IF(k2<6;f;IF(k2<7;g)))))))

The error dialog box opens and the number '1' in '=IF(k2<1;' is highlighted.

dlw

Welcome to Geeks to Go - Register now for FREE

**Geeks To Go** is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Started by
dlw
, Dec 15 2006 11:25 AM

Posted 15 December 2006 - 11:25 AM

=IF(k2<1;a;IF(k2<2;b;IF(k2<3;c;IF(k2<4;d;IF(k2<5;e;IF(k2<6;f;IF(k2<7;g)))))))

The error dialog box opens and the number '1' in '=IF(k2<1;' is highlighted.

dlw

Posted 15 December 2006 - 11:43 AM

Made the following changes;

=IF(K2<1,A:IF(K2<2,B:IF(K2<3,C:IF(K2<4,D:IF(K2<5,E:IF(K2<6,F:IF(K2<7,g)))))))

Now I get 'FALSE'.

I need the letter 'a,b,c,d,e,f,g,h,i,j' to show up.

dlw

=IF(K2<1,A:IF(K2<2,B:IF(K2<3,C:IF(K2<4,D:IF(K2<5,E:IF(K2<6,F:IF(K2<7,g)))))))

Now I get 'FALSE'.

I need the letter 'a,b,c,d,e,f,g,h,i,j' to show up.

dlw

Posted 15 December 2006 - 11:45 AM

Because

1) you used semi-colons instead of commas

2) your "true" values must be enclosed in quotation marks

Here's the corrected formula:

=IF(K2<1,"a",IF(K2<2,"b",IF(K2<3,"c",IF(K2<4,"d",IF(K2<5,"e",IF(K2<6,"f",IF(K2<7,"g")))))))

1) you used semi-colons instead of commas

2) your "true" values must be enclosed in quotation marks

Here's the corrected formula:

=IF(K2<1,"a",IF(K2<2,"b",IF(K2<3,"c",IF(K2<4,"d",IF(K2<5,"e",IF(K2<6,"f",IF(K2<7,"g")))))))

Posted 15 December 2006 - 01:35 PM

Thank you, that works great.

Now, I need the formula to go to 'o'.

After 'g' I can not add 'h,i,j,k,l,m,n,o'.

How do I overcome this?

dlw

Now, I need the formula to go to 'o'.

After 'g' I can not add 'h,i,j,k,l,m,n,o'.

How do I overcome this?

dlw

Posted 15 December 2006 - 01:47 PM

You will have to start another IF statement, or restructure the one you have now. ----You can only nest 7 IF statements in one cell.---- What exactly are you trying to do?

Posted 15 December 2006 - 01:48 PM

Posted 15 December 2006 - 01:58 PM

Look at this attachment.

Posted 15 December 2006 - 02:29 PM

I ship several products to the same zip code via UPS.

The only difference is the weight.

I have a chart that tells me the amount per pound.

I want to add a column to a ss that calculates the amount based on the pounds.

if(k2<=1,"9.47",if(k2<=2,"10.05"))

etc, etc. through 15 pounds.

The only difference is the weight.

I have a chart that tells me the amount per pound.

I want to add a column to a ss that calculates the amount based on the pounds.

if(k2<=1,"9.47",if(k2<=2,"10.05"))

etc, etc. through 15 pounds.

Posted 15 December 2006 - 02:34 PM

The formulas in that spreadsheet should work for you. When you use numbers instead of letters, you don't have to put quotation marks around them.

Posted 15 December 2006 - 03:14 PM

You really need to make a table with the weights and associated values and then use the LOOKUP syntax. Go to HELP to see how. If you need an example, let me know.

Posted 16 December 2006 - 04:42 AM

Thanks for the LOOKUP suggestion.

I'll start researching it now.

Yes, I'd like an example.

One can always use an example.

Thanks,

dlw

I'll start researching it now.

Yes, I'd like an example.

One can always use an example.

Thanks,

dlw

Posted 16 December 2006 - 04:52 AM

dlw,

nollijible's suggestion of a lookup formula is good and will work for both of the problems you presented.

There is also an alternative worksheet formula for your first problem

Since you are simply assigning an alpha character to each number you can use the CHAR formula as follows

in its basic form =CHAR(A1+97)

=IF(A1>-1,IF(A1<15,CHAR(A1+97),"out of range"))

An alternative for both problems is to create your own custom function using the Macro/VBA editor

if A1 contains the number you are checking

then B1 should contain the following formula =dlwTest(A1)

sample VBA code

Public Function dlwTest(IntDlw)

Dim StrDlw As String

Select Case IntDlw

Case 0

StrDlw = "A"

'you could do any number of calculations or checks here

Case 1

StrDlw = "B"

Case 2

StrDlw = "C"

Case 3

StrDlw = "D"

Case 4

StrDlw = "E"

Case Else

StrDlw = "Out of Range"

End Select

dlwTest = StrDlw

End Function

dsm

nollijible's suggestion of a lookup formula is good and will work for both of the problems you presented.

There is also an alternative worksheet formula for your first problem

Since you are simply assigning an alpha character to each number you can use the CHAR formula as follows

in its basic form =CHAR(A1+97)

=IF(A1>-1,IF(A1<15,CHAR(A1+97),"out of range"))

An alternative for both problems is to create your own custom function using the Macro/VBA editor

if A1 contains the number you are checking

then B1 should contain the following formula =dlwTest(A1)

sample VBA code

Public Function dlwTest(IntDlw)

Dim StrDlw As String

Select Case IntDlw

Case 0

StrDlw = "A"

'you could do any number of calculations or checks here

Case 1

StrDlw = "B"

Case 2

StrDlw = "C"

Case 3

StrDlw = "D"

Case 4

StrDlw = "E"

Case Else

StrDlw = "Out of Range"

End Select

dlwTest = StrDlw

End Function

dsm

Posted 16 December 2006 - 05:25 AM

dlw,

The attached file contains a VLOOKUP example

dsm
#### Attached Files

The attached file contains a VLOOKUP example

dsm

Posted 16 December 2006 - 07:09 AM

Attached is part of the ss I'm working on with the data and lookup table exactly where they should be.

I can not get it to work.

Any help appreciated.

thanks,

dlw

As you can see, the attachment isn't working either; crap.

What ranges have to be named?

I can not get it to work.

Any help appreciated.

thanks,

dlw

As you can see, the attachment isn't working either; crap.

What ranges have to be named?

**Edited by dlw, 16 December 2006 - 07:16 AM.**

Posted 16 December 2006 - 10:36 AM

Uhh...atachment? (It's always something

Try this example. I used VLOOKUP and a few IFs.

Let me know if you need help.
#### Attached Files

Try this example. I used VLOOKUP and a few IFs.

Let me know if you need help.

**Edited by nollijible, 16 December 2006 - 11:39 AM.**

0 members, 0 guests, 0 anonymous users

Community Forum Software by IP.Board

Licensed to: Geeks to Go, Inc.