Creating a Formula with multiple functions

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!

Perhaps =IF((I11*K11*M11)=0,0,IF(I11*K11*M11/1728<5,5,roundup((I11*K11*M11/1728),0))

How about:


Cheers,

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.

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.

jennyjo said:

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.

Click to expand...

You can resolve by adding an error check as below. This one sets the cell value to "" if there is an error:

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.

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

jennyjo said:

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

Click to expand...

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