I need to combine a formula and ROUNDUP to arrive at the next whole number value of a formula and do not want to burn a cell calculating the value of the formula before using ROUNDUP to reference the value in another cell.

I am building a table that will calculate materials needed for a project. As the same material is used to create two different components in the project I am tracking the total length of material needed in two separate cells before adding them together to get a combined total. As I can't buy partial units I'd like to SUM these two cells, divide by the material length, and ROUNDUP to arrive at a total number of material units needed for the project.

In this example D3 and E3 contain the values 448 and 240 respectively. F3 calculates the total number of units necessary with the formula "=SUM (D3:E3)/31" (where 31 is the length of the parent material at time of purchase) yielding a result of 22.19. I would like F3 to round this value to 23.

Any help is appreciated.

On Wed, 18 Nov 2015 14:22:19 -0800 (PST),
wrote:

I need to combine a formula and ROUNDUP to arrive at the next whole number value of a formula and do not want to burn a cell calculating the value of the formula before using ROUNDUP to reference the value in another cell.

I am building a table that will calculate materials needed for a project. As the same material is used to create two different components in the project I am tracking the total length of material needed in two separate cells before adding them together to get a combined total. As I can't buy partial units I'd like to SUM these two cells, divide by the material length, and ROUNDUP to arrive at a total number of material units needed for the project.

In this example D3 and E3 contain the values 448 and 240 respectively. F3 calculates the total number of units necessary with the formula "=SUM (D3:E3)/31" (where 31 is the length of the parent material at time of purchase) yielding a result of 22.19. I would like F3 to round this value to 23.

Any help is appreciated.

Am I missing something?

"=ROUNDUP((SUM (D3:E3)/31),0)"

or simply

"=ROUNDUP(SUM (D3:E3)/31)"

On Wednesday, November 18, 2015 at 11:44:14 PM UTC-6, pedro wrote:
On Wed, 18 Nov 2015 14:22:19 -0800 (PST),
wrote:

I need to combine a formula and ROUNDUP to arrive at the next whole number value of a formula and do not want to burn a cell calculating the value of the formula before using ROUNDUP to reference the value in another cell.

I am building a table that will calculate materials needed for a project.. As the same material is used to create two different components in the project I am tracking the total length of material needed in two separate cells before adding them together to get a combined total. As I can't buy partial units I'd like to SUM these two cells, divide by the material length, and ROUNDUP to arrive at a total number of material units needed for the project.

In this example D3 and E3 contain the values 448 and 240 respectively. F3 calculates the total number of units necessary with the formula "=SUM (D3:E3)/31" (where 31 is the length of the parent material at time of purchase) yielding a result of 22.19. I would like F3 to round this value to 23.

Any help is appreciated.

Am I missing something?

"=ROUNDUP((SUM (D3:E3)/31),0)"

or simply

"=ROUNDUP(SUM (D3:E3)/31)"

PEDRO -

Neither of these work. Though I thought I'd tried every variation of your suggestion before posting my original message, I'd obviously not tried the first one.

The first yields a value of "#NAME" in the cell. When the second is entered I get a an error message indicating that "there are too few arguments for this function".

Perhaps this has to do with the D3 and E3 cells? These cells contain formulas that yield numerical values rather than hard entered values.

On Thu, 19 Nov 2015 07:36:42 -0800 (PST),
wrote:


Neither of these work. Though I thought I'd tried every variation of your suggestion before posting my original message, I'd obviously not tried the first one.

The first yields a value of "#NAME" in the cell. When the second is entered I get a an error message indicating that "there are too few arguments for this function".

Perhaps this has to do with the D3 and E3 cells? These cells contain formulas that yield numerical values rather than hard entered values.

My error - the NAME# is triggered by the unintended space after SUM
which I copied from your post.

To test this out, I just entered a couple of random numbers (27.4 and
83.2) into two cells, and in D3 and E3 I copied these (=A3, and =B3).
With the formula =ROUNDUP((SUM(D3:E3)/31),0) in F3 I get 4.

The second suggested expression was based on the HELP description of
ROUNDUP, which infers the 0 (num_places) can be omitted but doesn't
show whether the "," is still required. I hadn't tested that, and yes
I get that error. It turns out that the comma separator IS required:
=ROUNDUP((SUM(D3:E3)/31),) works for me.

On Thursday, November 19, 2015 at 7:30:28 PM UTC-6, pedro wrote:
On Thu, 19 Nov 2015 07:36:42 -0800 (PST),
wrote:


Neither of these work. Though I thought I'd tried every variation of your suggestion before posting my original message, I'd obviously not tried the first one.

The first yields a value of "#NAME" in the cell. When the second is entered I get a an error message indicating that "there are too few arguments for this function".

Perhaps this has to do with the D3 and E3 cells? These cells contain formulas that yield numerical values rather than hard entered values.

My error - the NAME# is triggered by the unintended space after SUM
which I copied from your post.

To test this out, I just entered a couple of random numbers (27.4 and
83.2) into two cells, and in D3 and E3 I copied these (=A3, and =B3).
With the formula =ROUNDUP((SUM(D3:E3)/31),0) in F3 I get 4.

The second suggested expression was based on the HELP description of
ROUNDUP, which infers the 0 (num_places) can be omitted but doesn't
show whether the "," is still required. I hadn't tested that, and yes
I get that error. It turns out that the comma separator IS required:
=ROUNDUP((SUM(D3:E3)/31),) works for me.

PEDRO,

Thank you so much for your help with this.I apologize for the delay in my response. I have been out of the office for a while. Thank you so much for your help on this. I simply don't know how I missed this. I'd received numerous different error messages before requesting help. As I have had this document open since I began it, I was able to go back through every variation I'd tried and found that in every case I'd installed the space. crazy how its always the smallest errors that hang one up.

On Fri, 20 Nov 2015 09:10:49 -0800 (PST),
wrote:

On Thursday, November 19, 2015 at 7:30:28 PM UTC-6, pedro wrote:
On Thu, 19 Nov 2015 07:36:42 -0800 (PST),
wrote:


Neither of these work. Though I thought I'd tried every variation of your suggestion before posting my original message, I'd obviously not tried the first one.

The first yields a value of "#NAME" in the cell. When the second is entered I get a an error message indicating that "there are too few arguments for this function".

Perhaps this has to do with the D3 and E3 cells? These cells contain formulas that yield numerical values rather than hard entered values.

My error - the NAME# is triggered by the unintended space after SUM
which I copied from your post.

To test this out, I just entered a couple of random numbers (27.4 and
83.2) into two cells, and in D3 and E3 I copied these (=A3, and =B3).
With the formula =ROUNDUP((SUM(D3:E3)/31),0) in F3 I get 4.

The second suggested expression was based on the HELP description of
ROUNDUP, which infers the 0 (num_places) can be omitted but doesn't
show whether the "," is still required. I hadn't tested that, and yes
I get that error. It turns out that the comma separator IS required:
=ROUNDUP((SUM(D3:E3)/31),) works for me.

PEDRO,

Thank you so much for your help with this.I apologize for the delay in my response. I have been out of the office for a while. Thank you so much for your help on this. I simply don't know how I missed this. I'd received numerous different error messages before requesting help. As I have had this document open since I began it, I was able to go back through every variation I'd tried and found that in every case I'd installed the space. crazy how its always the smallest errors that hang one up.

You're welcome. I normally stay out of suggesting solutions as there
are real Excel gurus about who know infinitely more than me. I only
popped in because it seemed an easy requirement.