Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
On Sat, 29 May 2010 10:52:01 -0700, RichM
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
=IF(O3=1,"7",IF(O3=2,"6",IF(O3=3,"5",IF(O3=4,"4",I F(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 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 . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
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",I F(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 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 . |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
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",I F(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 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 . . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
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",I F(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 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 . . |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formula yeilds non-number result
=IF(O3=1,"7",IF(O3=2,"6",IF(O3=3,"5",IF(O3=4,"4",I F(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",I F(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 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Against a Formula - not it's result | Excel Discussion (Misc queries) | |||
How can I convert the result of a formula to a whole number? | Excel Discussion (Misc queries) | |||
conditional formatting based on another cells formula result | Excel Discussion (Misc queries) | |||
Is it possible to change the "result of a formula" to a "number? | Excel Discussion (Misc queries) | |||
Is it possible to change the "result of a formula" to a "number? | Excel Discussion (Misc queries) |