A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional formula yeilds non-number result



 
 
Thread Tools Display Modes
  #1  
Old May 29th, 2010, 06:52 PM posted to microsoft.public.excel.worksheet.functions
RichM
external usenet poster
 
Posts: 33
Default 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  
Old May 29th, 2010, 07:14 PM posted to microsoft.public.excel.worksheet.functions
Lars-Åke Aspelin[_4_]
external usenet poster
 
Posts: 74
Default 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  
Old May 30th, 2010, 10:57 AM posted to microsoft.public.excel.worksheet.functions
RichM
external usenet poster
 
Posts: 33
Default 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  
Old May 30th, 2010, 11:17 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old May 30th, 2010, 02:20 PM posted to microsoft.public.excel.worksheet.functions
RichM
external usenet poster
 
Posts: 33
Default 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  
Old May 30th, 2010, 04:28 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old May 30th, 2010, 04:36 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default 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
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:35 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.