Calculated values in a cell
 

Hello All,

I am trying to use this formula
=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",I F(AG3=2,"2011",IF(AG3=1,"2012")))))
to assign a year value to the numbers 1,2,3,4, and 5. These numbers are the
product of the following formula:
=IF(AF32.6,"5",IF(AF32.01,"4",IF(AF31.42,"3",IF (AF30.83,"2",IF(AF30.24,"1",IF(AF3<0.01,"Beyond
5 Years")))))). When I write the formula, the result is "FALSE". When I use
the same formula in cells with just numeric values, it works, and I get the
correspondent years. Do I get "FALSE" because I am referring to cells with
formula in it? I formatted the 1,2,3, 4, and 5 column as general and
numeric and still have the same problem. I need to keep the second formula
in place because this data is subject to changes depending on scores. Is
there a way around this? I can provide an example if necessary. I tried a
vlookup and hlookup with no luck.

Thank you,

Frank

Calculated values in a cell
 

Remove *all* the quotes from around any numbers.

=IF(AG3=5,"2008",.....
=IF(AF32.6,"5",.....

Should be:

=IF(AG3=5,2008,
=IF(AF32.6,5,

We can shorten this one:

=IF(AG3=5,2008,IF(AG3=4,2009,IF(AG3=3,2010,IF(AG3= 2,2011,IF(AG3=1,2012)))))

To:

=CHOOSE(AG3,2012,2011,2010,2009,2008)

We could shorten the other one too but I'm not following the logic. You have
an unaccounted for gap from 0.01 to 0.24. If AF3 is in that gap then you'll
get a result of FALSE.

--
Biff
Microsoft Excel MVP


"frankobl3" wrote in message
...
Hello All,

I am trying to use this formula
=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",I F(AG3=2,"2011",IF(AG3=1,"2012")))))
to assign a year value to the numbers 1,2,3,4, and 5. These numbers are
the product of the following formula:
=IF(AF32.6,"5",IF(AF32.01,"4",IF(AF31.42,"3",IF (AF30.83,"2",IF(AF30.24,"1",IF(AF3<0.01,"Beyond
5 Years")))))). When I write the formula, the result is "FALSE". When I
use the same formula in cells with just numeric values, it works, and I
get the correspondent years. Do I get "FALSE" because I am referring to
cells with formula in it? I formatted the 1,2,3, 4, and 5 column as
general and numeric and still have the same problem. I need to keep the
second formula in place because this data is subject to changes depending
on scores. Is there a way around this? I can provide an example if
necessary. I tried a vlookup and hlookup with no luck.

Thank you,

Frank

Calculated values in a cell
 

Hi,
Two things to consider.
Firstly, you haven't specified what the first formula is to return if AG3 is
not 1, 2, 3, 4, or 5, hence the FALSE.
Try
=IF(AG3=5,2008,IF(AG3=4,2009,IF(AG3=3,2010,IF(AG3= 2,2011,IF(AG3=1,2012,""))))
)

=IF(AF32.6,5,IF(AF32.01,4,IF(AF31.42,3,IF(AF30 .83,2,IF(AF30.24,1,
IF(AF3<0.01,"Beyond 5 Years"))))))

Secondly, by enclosing the "1", "2" etc and the "2008", "2009" etc in quotes,
you are forcing them to be text.

Dave

url:http://www.ureader.com/msg/10356802.aspx

Calculated values in a cell
 

Thank you guys,

I used Biff's proposed shorten solution and it worked. When I removed all
quotes from the numbers I got a "#VALUE" error, I'll keep trying that
solution. Nevertheless I got the results I needed. Thank you again.

Frank

"T. Valko" wrote in message
...
Remove *all* the quotes from around any numbers.

=IF(AG3=5,"2008",.....
=IF(AF32.6,"5",.....

Should be:

=IF(AG3=5,2008,
=IF(AF32.6,5,

We can shorten this one:

=IF(AG3=5,2008,IF(AG3=4,2009,IF(AG3=3,2010,IF(AG3= 2,2011,IF(AG3=1,2012)))))

To:

=CHOOSE(AG3,2012,2011,2010,2009,2008)

We could shorten the other one too but I'm not following the logic. You
have an unaccounted for gap from 0.01 to 0.24. If AF3 is in that gap then
you'll get a result of FALSE.

--
Biff
Microsoft Excel MVP


"frankobl3" wrote in message
...
Hello All,

I am trying to use this formula
=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",I F(AG3=2,"2011",IF(AG3=1,"2012")))))
to assign a year value to the numbers 1,2,3,4, and 5. These numbers are
the product of the following formula:
=IF(AF32.6,"5",IF(AF32.01,"4",IF(AF31.42,"3",IF (AF30.83,"2",IF(AF30.24,"1",IF(AF3<0.01,"Beyond
5 Years")))))). When I write the formula, the result is "FALSE". When I
use the same formula in cells with just numeric values, it works, and I
get the correspondent years. Do I get "FALSE" because I am referring to
cells with formula in it? I formatted the 1,2,3, 4, and 5 column as
general and numeric and still have the same problem. I need to keep the
second formula in place because this data is subject to changes depending
on scores. Is there a way around this? I can provide an example if
necessary. I tried a vlookup and hlookup with no luck.

Thank you,

Frank

Calculated values in a cell
 

You need to fix this formula:

=IF(AF32.6,5,IF(AF32.01,4,IF(AF31.42,3,IF(AF30 .83,2,IF(AF30.24,1,IF(AF3<0.01,"Beyond
5 Years"))))))

If AF3 is in the range 0.01 to 0.24 then that formula will return FALSE and
will also cause the other formula to return an error.

So, let's change the other formula to handle that:

=IF(COUNT(AG3),CHOOSE(AG3,2012,2011,2010,2009,2008 ),"")

--
Biff
Microsoft Excel MVP


"frankobl3" wrote in message
...
Thank you guys,

I used Biff's proposed shorten solution and it worked. When I removed
all quotes from the numbers I got a "#VALUE" error, I'll keep trying that
solution. Nevertheless I got the results I needed. Thank you again.

Frank

"T. Valko" wrote in message
...
Remove *all* the quotes from around any numbers.

=IF(AG3=5,"2008",.....
=IF(AF32.6,"5",.....

Should be:

=IF(AG3=5,2008,
=IF(AF32.6,5,

We can shorten this one:

=IF(AG3=5,2008,IF(AG3=4,2009,IF(AG3=3,2010,IF(AG3= 2,2011,IF(AG3=1,2012)))))

To:

=CHOOSE(AG3,2012,2011,2010,2009,2008)

We could shorten the other one too but I'm not following the logic. You
have an unaccounted for gap from 0.01 to 0.24. If AF3 is in that gap then
you'll get a result of FALSE.

--
Biff
Microsoft Excel MVP


"frankobl3" wrote in message
...
Hello All,

I am trying to use this formula
=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",I F(AG3=2,"2011",IF(AG3=1,"2012")))))
to assign a year value to the numbers 1,2,3,4, and 5. These numbers are
the product of the following formula:
=IF(AF32.6,"5",IF(AF32.01,"4",IF(AF31.42,"3",IF (AF30.83,"2",IF(AF30.24,"1",IF(AF3<0.01,"Beyond
5 Years")))))). When I write the formula, the result is "FALSE". When I
use the same formula in cells with just numeric values, it works, and I
get the correspondent years. Do I get "FALSE" because I am referring to
cells with formula in it? I formatted the 1,2,3, 4, and 5 column as
general and numeric and still have the same problem. I need to keep the
second formula in place because this data is subject to changes
depending on scores. Is there a way around this? I can provide an
example if necessary. I tried a vlookup and hlookup with no luck.

Thank you,

Frank

Calculated values in a cell
 

On Tue, 11 Nov 2008 22:16:20 -0500, "frankobl3" wrote:

=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010", IF(AG3=2,"2011",IF(AG3=1,"2012")))))

This looks as if it should return the same value as your formula:

=2013-AG3

Or, for error testing to be sure there is a valid number in AG3:

=if(and(ag3=1,ag3=5),2013-ag3,"ag3 has invalid value")

--ron