Upcoming Power Excel Seminars
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
    45,158
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 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
    45,158
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 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
    45,158
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    4 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

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
  •  

 

DMCA.com