combining formulas

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 are:

=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 are:
>
> =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 are:
> >
> > =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 are:
> > >
> > > =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