I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
..1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

=IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))

I'm not sure if an em dash is two hyphens, so if it's not, replace the
characters between the double quotation marks with an em dash.

Dave

--
Brevity is the soul of wit.


"JVRB" wrote:

> I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

That is what I was using and it doesn't work. Here's the actual formula:

=IF(AND(N24/C10*100>(0.1),N24/C10*100<0.1),"—",ROUND(N24/C10*100,1))

It does not return an em dash. It appears to ignore the and array and go to
the rounding formula. Do I need to use brackets around the And statement?

"Dave F" wrote:

> =IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))
>
> I'm not sure if an em dash is two hyphens, so if it's not, replace the
> characters between the double quotation marks with an em dash.
>
> Dave
>
> --
> Brevity is the soul of wit.
>
>
> "JVRB" wrote:
>
> > I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> > .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

What are the values in N24 and C10 which do not return the em dash?

Dave
--
Brevity is the soul of wit.


"JVRB" wrote:

> That is what I was using and it doesn't work. Here's the actual formula:
>
> =IF(AND(N24/C10*100>(0.1),N24/C10*100<0.1),"—",ROUND(N24/C10*100,1))
>
> It does not return an em dash. It appears to ignore the and array and go to
> the rounding formula. Do I need to use brackets around the And statement?
>
> "Dave F" wrote:
>
> > =IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))
> >
> > I'm not sure if an em dash is two hyphens, so if it's not, replace the
> > characters between the double quotation marks with an em dash.
> >
> > Dave
> >
> > --
> > Brevity is the soul of wit.
> >
> >
> > "JVRB" wrote:
> >
> > > I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> > > .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

With the actual formula:

=IF(AND(N24/C10*100<0.1,N24/C10*100>(0.1)),"—",ROUND(N24/C10*100,1))

Where N24 is 1,200 and C10 is 4,791,299, I get .02 on the old mechcanical
adding machine and .02 is definitely between -.1 and.1, so I'm not sure where
the wrinkle is. Excel doesn't show an error in the formula. It just doesn't
return the correct result.

"Dave F" wrote:

> =IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))
>
> I'm not sure if an em dash is two hyphens, so if it's not, replace the
> characters between the double quotation marks with an em dash.
>
> Dave
>
> --
> Brevity is the soul of wit.
>
>
> "JVRB" wrote:
>
> > I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> > .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

I figured it out.

Get rid of the parentheses aroung the negative 1 and just put -1 there.
Recalculate.

Dave
--
Brevity is the soul of wit.


"JVRB" wrote:

> With the actual formula:
>
> =IF(AND(N24/C10*100<0.1,N24/C10*100>(0.1)),"—",ROUND(N24/C10*100,1))
>
> Where N24 is 1,200 and C10 is 4,791,299, I get .02 on the old mechcanical
> adding machine and .02 is definitely between -.1 and.1, so I'm not sure where
> the wrinkle is. Excel doesn't show an error in the formula. It just doesn't
> return the correct result.
>
> "Dave F" wrote:
>
> > =IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))
> >
> > I'm not sure if an em dash is two hyphens, so if it's not, replace the
> > characters between the double quotation marks with an em dash.
> >
> > Dave
> >
> > --
> > Brevity is the soul of wit.
> >
> >
> > "JVRB" wrote:
> >
> > > I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> > > .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

That did the trick. Thanks!

"Dave F" wrote:

> I figured it out.
>
> Get rid of the parentheses aroung the negative 1 and just put -1 there.
> Recalculate.
>
> Dave
> --
> Brevity is the soul of wit.
>
>
> "JVRB" wrote:
>
> > With the actual formula:
> >
> > =IF(AND(N24/C10*100<0.1,N24/C10*100>(0.1)),"—",ROUND(N24/C10*100,1))
> >
> > Where N24 is 1,200 and C10 is 4,791,299, I get .02 on the old mechcanical
> > adding machine and .02 is definitely between -.1 and.1, so I'm not sure where
> > the wrinkle is. Excel doesn't show an error in the formula. It just doesn't
> > return the correct result.
> >
> > "Dave F" wrote:
> >
> > > =IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))
> > >
> > > I'm not sure if an em dash is two hyphens, so if it's not, replace the
> > > characters between the double quotation marks with an em dash.
> > >
> > > Dave
> > >
> > > --
> > > Brevity is the soul of wit.
> > >
> > >
> > > "JVRB" wrote:
> > >
> > > > I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> > > > .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)

No problem.

My guess is that it is good practice to use the negative sign to denote
negative numbers in formulas, as opposed to using parentheses.

Dave
--
Brevity is the soul of wit.


"JVRB" wrote:

> That did the trick. Thanks!
>
> "Dave F" wrote:
>
> > I figured it out.
> >
> > Get rid of the parentheses aroung the negative 1 and just put -1 there.
> > Recalculate.
> >
> > Dave
> > --
> > Brevity is the soul of wit.
> >
> >
> > "JVRB" wrote:
> >
> > > With the actual formula:
> > >
> > > =IF(AND(N24/C10*100<0.1,N24/C10*100>(0.1)),"—",ROUND(N24/C10*100,1))
> > >
> > > Where N24 is 1,200 and C10 is 4,791,299, I get .02 on the old mechcanical
> > > adding machine and .02 is definitely between -.1 and.1, so I'm not sure where
> > > the wrinkle is. Excel doesn't show an error in the formula. It just doesn't
> > > return the correct result.
> > >
> > > "Dave F" wrote:
> > >
> > > > =IF(AND(E12/E10*100>-.1,E12/E10*100<.1),"--",ROUND(E12/E10*100,1))
> > > >
> > > > I'm not sure if an em dash is two hyphens, so if it's not, replace the
> > > > characters between the double quotation marks with an em dash.
> > > >
> > > > Dave
> > > >
> > > > --
> > > > Brevity is the soul of wit.
> > > >
> > > >
> > > > "JVRB" wrote:
> > > >
> > > > > I need to do a formua where IF E12/E10*100 is greater than (.1) AND less than
> > > > > .1, Excel will return an em dash, otherwise (ROUND(E12/E10*100,1)