in [Functions]

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 > > . > > |