Hi,

I'm using a formula to add a series of values but need to add a rounding
function to the result so that I can reduce the number of calculated fields
in a spreadsheet.

The formulas I want to combine a

=IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8)) -- this result is displayed in
cell E92

=IF(E92/$C$143<=0.1,, CEILING(E92/$C$143,0.1))

Anyone know how to get the rounding to fit into the first formula? I can't
seem to get away from the circular reference, and when I try to nest the
formulas I get a series of errors...

Help. Many thanks. Sherri

Hi Sherri, try to substitute the E92 on the second formula for the 1st
formula you have.

eg.

=IF(IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8))/$C$143<=0.1,,
CEILING(IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8))/$C$143,0.1))

hth
regards from Brazil
Marcelo


"Sherri" escreveu:

Hi,

I'm using a formula to add a series of values but need to add a rounding
function to the result so that I can reduce the number of calculated fields
in a spreadsheet.

The formulas I want to combine a

=IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8)) -- this result is displayed in
cell E92

=IF(E92/$C$143<=0.1,, CEILING(E92/$C$143,0.1))

Anyone know how to get the rounding to fit into the first formula? I can't
seem to get away from the circular reference, and when I try to nest the
formulas I get a series of errors...

Help. Many thanks. Sherri

Amazing (and so fast!). I should have come here first instead of banging my
head against the wall for two days :)

Thanks so much!

Cheers.
Sherri

"Marcelo" wrote:

Hi Sherri, try to substitute the E92 on the second formula for the 1st
formula you have.

eg.

=IF(IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8))/$C$143<=0.1,,
CEILING(IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8))/$C$143,0.1))

hth
regards from Brazil
Marcelo


"Sherri" escreveu:

Hi,

I'm using a formula to add a series of values but need to add a rounding
function to the result so that I can reduce the number of calculated fields
in a spreadsheet.

The formulas I want to combine a

=IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8)) -- this result is displayed in
cell E92

=IF(E92/$C$143<=0.1,, CEILING(E92/$C$143,0.1))

Anyone know how to get the rounding to fit into the first formula? I can't
seem to get away from the circular reference, and when I try to nest the
formulas I get a series of errors...

Help. Many thanks. Sherri

thanks for the feedback

glad to help
regards from Brazil
Marcelo

"Sherri" escreveu:

Amazing (and so fast!). I should have come here first instead of banging my
head against the wall for two days :)

Thanks so much!

Cheers.
Sherri

"Marcelo" wrote:

Hi Sherri, try to substitute the E92 on the second formula for the 1st
formula you have.

eg.

=IF(IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8))/$C$143<=0.1,,
CEILING(IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8))/$C$143,0.1))

hth
regards from Brazil
Marcelo


"Sherri" escreveu:

Hi,

I'm using a formula to add a series of values but need to add a rounding
function to the result so that I can reduce the number of calculated fields
in a spreadsheet.

The formulas I want to combine a

=IF((SUM(E7:E19)-E8)<=0.1,,(SUM(E7:E19)-E8)) -- this result is displayed in
cell E92

=IF(E92/$C$143<=0.1,, CEILING(E92/$C$143,0.1))

Anyone know how to get the rounding to fit into the first formula? I can't
seem to get away from the circular reference, and when I try to nest the
formulas I get a series of errors...

Help. Many thanks. Sherri