Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: convert formula to code please

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default convert formula to code please

    is there a way that I can convert this formula to code? it seems it is too long now and the last part of the formula does not work

    =IF(B14="cedar conversion",ROUNDUP(B16/10,0),IF(B14="plywood over shiplap tear off",ROUNDUP(B16/10,0),IF(B14="2 layer plywood over shiplap",ROUNDUP(B16/10,0),IF(B14="3 layer plywood over shiplap",ROUNDUP(B16/10,0),IF(B14="1 layer on cedar tear off",ROUNDUP(B16/10,0),IF(B14="2 layer on cedar tear off",ROUNDUP(B16/10,0),IF(B14="3 layer on cedar tear off",ROUNDUP(B16/10,0),"")))))))

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,640
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    5 Thread(s)

    Default Re: convert formula to code please

    Instead of nesting it, since all the conditions are resulting in the same calculation, why not use OR, i.e.
    =IF(OR(condition1, condition2, condition3, ...),ROUNDUP(B16/10,0),"")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert formula to code please

    Quote Originally Posted by d0wnt0wn View Post
    is there a way that I can convert this formula to code? it seems it is too long now and the last part of the formula does not work

    =IF(B14="cedar conversion",ROUNDUP(B16/10,0),IF(B14="plywood over shiplap tear off",ROUNDUP(B16/10,0),IF(B14="2 layer plywood over shiplap",ROUNDUP(B16/10,0),IF(B14="3 layer plywood over shiplap",ROUNDUP(B16/10,0),IF(B14="1 layer on cedar tear off",ROUNDUP(B16/10,0),IF(B14="2 layer on cedar tear off",ROUNDUP(B16/10,0),IF(B14="3 layer on cedar tear off",ROUNDUP(B16/10,0),"")))))))
    How many conditions do you have where your don't round B16/10? It may be easier to write a formula when those conditions are not met....
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  4. #4
    Board Regular
    Join Date
    Oct 2002
    Posts
    747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert formula to code please

    its about the same amount... let me try the 1f(or( way as suggested and I will report back

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Posts
    747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert formula to code please

    Hi Joe it returns a #value error using that method

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    Wisconsin
    Posts
    2,350
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert formula to code please

    Quote Originally Posted by d0wnt0wn View Post
    Hi Joe it returns a #value error using that method
    What is in B16 when you get the error?
    Using Excel 2013

    "Short answer yes with an if, long answer no with a but."
    -Reverend Lovejoy, The Simpsons

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,640
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    5 Thread(s)

    Default Re: convert formula to code please

    Hi Joe it returns a #value error using that method
    Also, please post your formula attempt, as well as the values in B14 and B16.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Oct 2002
    Posts
    747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert formula to code please

    Hi Joe

    Here is the formula I tried =IF(OR("Cedar conversion"),ROUNDUP(B16/10,0),"")

    Cedar conversion is in cel B14

    50 is in cel B16

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,640
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    5 Thread(s)

    Default Re: convert formula to code please

    You forgot your B14 reference in your formula.
    Also, there is no sense in using OR with only one condition. I assume you will be adding your other conditions, i.e.
    =IF(OR(B14="Cedar conversion",B14="plywood over shiplap tear off",B14="2 layer plywood over shiplap",...),ROUNDUP(B16/10,0),"")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Oct 2002
    Posts
    747
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert formula to code please

    oops... thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •