Results 1 to 9 of 9

Thread: Creating a Formula with multiple functions

  1. #1

    Creating a Formula with multiple functions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to create a formula that will take 3 dimensions (in inches) from individual cells, multiply them to come up with cubic inches, divide by 1728 to come up with cubic feet, then ROUNDUP that number to the nearest whole number. Here's where it gets tricky... I then need to make sure that IF the result is less than 5, it will give a result of 5, and IF the dimension cells are blank it will default to 0 or be left blank. I am able to get the total cubic feet to ROUNDUP and one of the IFs, but not both. Or I am able to get both IFs, but not the ROUNDUP. No matter what I try, I keep getting an error message. Here is one of the formulas that works.
    =IF((I11*K11*M11)<1,0,IF(I11*K11*M11/1728<5,5)) But if I try to add the ROUNDUP function I get an error message. Please help!
    Last edited by jennyjo; 2015-06-26 at 03:59 AM.

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps =IF((I11*K11*M11)=0,0,IF(I11*K11*M11/1728<5,5,roundup((I11*K11*M11/1728),0))

  3. #3
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    121
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    How about:

    Code:
    =IF((I11*K11*M11)=0,0,ROUNDUP(MAX(I11*K11*M11/1728,5),0))
    Cheers,

  4. #4

    Still having problems...

    Hi Pecoflyer. Your formula works great until I try to copy it into the cells below. If there are no dimensions in the cells that are included in the formula, then the formula returns an error message. Do you know how to solve this? Thanks for your help!

    I tried to send a screenshot, but this site will not allow it until my "post count is 5 or greater". I hope my description is enough to help you understand my problem.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screenshot 2015-06-27 15.55.08.png 
Views:	5 
Size:	96.4 KB 
ID:	3628  

  5. #5
    Hi CheshireCat. Your formula works great until I try to copy it into the cells below. If there are no dimensions in the cells that are included in the formula, then the formula returns an error message. Do you know how to solve this? Thanks for your help!

    I tried to send a screenshot, but this site will not allow it until my "post count is 5 or greater". I hope my description is enough to help you understand my problem.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by jennyjo View Post
    Hi CheshireCat. Your formula works great until I try to copy it into the cells below. If there are no dimensions in the cells that are included in the formula, then the formula returns an error message. Do you know how to solve this? Thanks for your help!

    I tried to send a screenshot, but this site will not allow it until my "post count is 5 or greater". I hope my description is enough to help you understand my problem.
    You can resolve by adding an error check as below. This one sets the cell value to "" if there is an error:

    Code:
    =IFERROR(IF((I11*K11*M11)=0,0,ROUNDUP(MAX(I11*K11*M11/1728,5),0)),"")

  7. #7
    Hi Hercules, YOU'RE A GENIUS! It worked, but the next step in my calculations is to take that number and multiply it by a dollar amount to arrive at a total cost. I'm having the same problem with the error message. I tried to follow the logic of your formula to create a similar formula for this function, but to no avail. I feel like an idiot because I can't figure it out. I've tried everything. I tried =IFERROR(IF(N11*O11)=0,0,IF (N11*O11)=0),"") but still keep getting an error with my formula. What am I missing here??? Not sure if the screenshot will come through, but it might help if you can see it.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Screenshot 2015-06-28 13.23.08.png 
Views:	4 
Size:	91.2 KB 
ID:	3629  

  8. #8
    Hi Hercules, Thanks! I figured it out. It was much simpler than I thought. My worksheet is complete. Thanks to everyone!!!

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by jennyjo View Post
    Hi Hercules, Thanks! I figured it out. It was much simpler than I thought. My worksheet is complete. Thanks to everyone!!!
    Hi jennyjo
    Im sorry its taken me a while to pick up your response. The IFERROR function is a variation on the IF function that was necessary because with IF only the format was IF ( ISERROR [MyFormula] THEN "" ELSE [MyFormula]. The new IFERROR function introduced in 2007 cleverly eliminates the need to type out and process the formula twice with:
    IFERROR([MyFormula] THEN "". If there is no error the result of MyFormula is returned automatically.
    So... If you want to add an IFERROR check to a typical formula:
    At the start of your formula insert IFERROR(
    At the end add , "") Replacing the "" with whatever you want to return if an error is encountered.

    Hope that explains it, and your very welcome

Posting Permissions

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