Jump to content

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.

Create Account How it Works
Photo

Problem with formula


  • Please log in to reply

#1
dlw

dlw

    Member

  • Member
  • PipPipPip
  • 184 posts
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
  • 0

Advertisements


#2
dlw

dlw

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 184 posts
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
  • 0

#3
piper

piper

    Retired Staff

  • Retired Staff
  • 2,459 posts
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")))))))
  • 0

#4
dlw

dlw

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 184 posts
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
  • 0

#5
nollijible

nollijible

    Member

  • Member
  • PipPip
  • 25 posts
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?
  • 0

#6
jester620

jester620

    Member

  • Member
  • PipPipPip
  • 150 posts
I use this tutorial alot for my excel work. Hopefully it will help.

Clemson Univ. Tutorial
  • 0

#7
piper

piper

    Retired Staff

  • Retired Staff
  • 2,459 posts
Look at this attachment.
  • 0

#8
dlw

dlw

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 184 posts
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.
  • 0

#9
piper

piper

    Retired Staff

  • Retired Staff
  • 2,459 posts
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.
  • 0

#10
nollijible

nollijible

    Member

  • Member
  • PipPip
  • 25 posts
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.
  • 0

Advertisements


#11
dlw

dlw

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 184 posts
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
  • 0

#12
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
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
  • 0

#13
dsm

dsm

    Member

  • Member
  • PipPip
  • 98 posts
dlw,
The attached file contains a VLOOKUP example



dsm

Attached Files


  • 0

#14
dlw

dlw

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 184 posts
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.

  • 0

#15
nollijible

nollijible

    Member

  • Member
  • PipPip
  • 25 posts
Uhh...atachment? (It's always something :whistling:

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

Let me know if you need help.

Attached Files


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

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP