Prev: Conditional formula
Next: countblank with sumproduct?
From: RichM on 29 May 2010 13:52
Hello,

I made conditional formulas but with the numbers that result I am unable to
calculate their average. I get the #DIV/0! error. They are formatted as
"General" but even when I format them as numbers they cannot be averaged. I
have to copy them and "paste special" them as values, then there is an error
message that pops up (the little green triangle in the upper left of the cell
that I click on) where I can convert them to numbers.

Is there a way to make them numbers when they are the result of conditional
formulas without going through all those steps?

Thank you.
From: Lars-�ke Aspelin on 29 May 2010 14:14
On Sat, 29 May 2010 10:52:01 -0700, RichM
<RichM(a)discussions.microsoft.com> wrote:

>Hello,
>
>I made conditional formulas but with the numbers that result I am unable to
>calculate their average. I get the #DIV/0! error. They are formatted as
>"General" but even when I format them as numbers they cannot be averaged. I
>have to copy them and "paste special" them as values, then there is an error
>message that pops up (the little green triangle in the upper left of the cell
>that I click on) where I can convert them to numbers.
>
>Is there a way to make them numbers when they are the result of conditional
>formulas without going through all those steps?
>
>Thank you.

Post an example of your "conditional formula".

Lars-�ke
From: RichM on 30 May 2010 05:57
=IF(O3=1,"7",IF(O3=2,"6",IF(O3=3,"5",IF(O3=4,"4",IF(O3=5,"3",IF(O3=6,"2",IF(O3=7,"1")))))))

This works and I have a column of them but when I try to average the column
it doesn't work.

Thank you


"Lars-Åke Aspelin" wrote:

> On Sat, 29 May 2010 10:52:01 -0700, RichM
> <RichM(a)discussions.microsoft.com> wrote:
>
> >Hello,
> >
> >I made conditional formulas but with the numbers that result I am unable to
> >calculate their average. I get the #DIV/0! error. They are formatted as
> >"General" but even when I format them as numbers they cannot be averaged. I
> >have to copy them and "paste special" them as values, then there is an error
> >message that pops up (the little green triangle in the upper left of the cell
> >that I click on) where I can convert them to numbers.
> >
> >Is there a way to make them numbers when they are the result of conditional
> >formulas without going through all those steps?
> >
> >Thank you.
>
> Post an example of your "conditional formula".
>
> Lars-Åke
> .
>
From: Roger Govier on 30 May 2010 06:17
Hi Rich

Remove the " " around each of your numbers.
You are forcing them to be Text and not Numeric

--
Regards
Roger Govier

RichM wrote:
> =IF(O3=1,"7",IF(O3=2,"6",IF(O3=3,"5",IF(O3=4,"4",IF(O3=5,"3",IF(O3=6,"2",IF(O3=7,"1")))))))
>
> This works and I have a column of them but when I try to average the column
> it doesn't work.
>
> Thank you
>
>
> "Lars-Åke Aspelin" wrote:
>
>> On Sat, 29 May 2010 10:52:01 -0700, RichM
>> <RichM(a)discussions.microsoft.com> wrote:
>>
>>> Hello,
>>>
>>> I made conditional formulas but with the numbers that result I am unable to
>>> calculate their average. I get the #DIV/0! error. They are formatted as
>>> "General" but even when I format them as numbers they cannot be averaged. I
>>> have to copy them and "paste special" them as values, then there is an error
>>> message that pops up (the little green triangle in the upper left of the cell
>>> that I click on) where I can convert them to numbers.
>>>
>>> Is there a way to make them numbers when they are the result of conditional
>>> formulas without going through all those steps?
>>>
>>> Thank you.
>> Post an example of your "conditional formula".
>>
>> Lars-Åke
>> .
>>
From: RichM on 30 May 2010 09:20
Thanks Roger. That worked. So is it true that when you have quotes around a
number it is formatted as text even if a number appears as the product of the
condition?

Thanks again.

"Roger Govier" wrote:

> Hi Rich
>
> Remove the " " around each of your numbers.
> You are forcing them to be Text and not Numeric
>
> --
> Regards
> Roger Govier
>
> RichM wrote:
> > =IF(O3=1,"7",IF(O3=2,"6",IF(O3=3,"5",IF(O3=4,"4",IF(O3=5,"3",IF(O3=6,"2",IF(O3=7,"1")))))))
> >
> > This works and I have a column of them but when I try to average the column
> > it doesn't work.
> >
> > Thank you
> >
> >
> > "Lars-Ã…ke Aspelin" wrote:
> >
> >> On Sat, 29 May 2010 10:52:01 -0700, RichM
> >> <RichM(a)discussions.microsoft.com> wrote:
> >>
> >>> Hello,
> >>>
> >>> I made conditional formulas but with the numbers that result I am unable to
> >>> calculate their average. I get the #DIV/0! error. They are formatted as
> >>> "General" but even when I format them as numbers they cannot be averaged. I
> >>> have to copy them and "paste special" them as values, then there is an error
> >>> message that pops up (the little green triangle in the upper left of the cell
> >>> that I click on) where I can convert them to numbers.
> >>>
> >>> Is there a way to make them numbers when they are the result of conditional
> >>> formulas without going through all those steps?
> >>>
> >>> Thank you.
> >> Post an example of your "conditional formula".
> >>
> >> Lars-Ã…ke
> >> .
> >>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Conditional formula
Next: countblank with sumproduct?