[dlw]

Why won't the following formula work in MS Excel?

=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

[Advertisements]

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

[dlw]

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

[piper]

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

[dlw]

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

[nollijible]

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?

[jester620]

I use this tutorial alot for my excel work. Hopefully it will help.

Clemson Univ. Tutorial

[piper]

Look at this attachment.

[dlw]

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.

[piper]

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.

[nollijible]

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.

[dlw]

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

[dsm]

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

[dsm]

dlw,
The attached file contains a VLOOKUP example



dsm

Attached Files

•  GTG_dlw.zip   13.17KB   211 downloads

[dlw]

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?

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

[nollijible]

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

•  VLOOKUP_EXAMPLE.zip   2.64KB   230 downloads

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