Prev: Conditional formula
Next: countblank with sumproduct?
From: Roger Govier on 30 May 2010 11:28
Hi Rich
Yes it is.
At it's simplest
=IF(O3=1,7,"")
If there is a 1 in cell O3, then there will be a 7 in the cell with the
formula, otherwise "" a null value
Or
=IF(O3=1,7,0) would put a 0 rather than null if the condition is False.

If Cell O3 contained something like 1BCD, and you had a formula to
extract the 1 like
=LEFT(O3,1)
that again would return 1 as a text value , as you are using a Text
function.
You can force it to return a Numeric result by using either
=VALUE(LEFT(O3,1)) or
=--LEFT(O3,1)
where the double unary minus -- coerces the value from Text to Numeric.

--
Regards
Roger Govier

RichM wrote:
> 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
>>>> .
>>>>
>> .
>>
From: Teethless mama on 30 May 2010 11:36
>
=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")))))))

Remove double quotes around the numbers.

Or you can try this elegant solution.

=8-O3


"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
> > .
> >
First  |  Prev  | 
Pages: 1 2
Prev: Conditional formula
Next: countblank with sumproduct?