Calculated values in a cell

[frankobl3]

Hello All,

I am trying to use this formula
=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",IF(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(AF3>2.6,"5",IF(AF3>2.01,"4",IF(AF3>1.42,"3",IF(AF3>0.83,"2",IF(AF3>0.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

 

[T. Valko]

Remove *all* the quotes from around any numbers.

=IF(AG3=5,"2008",.....
=IF(AF3>2.6,"5",.....

Should be:

=IF(AG3=5,2008,
=IF(AF3>2.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.

 

[Dave Curtis]

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(AF3>2.6,5,IF(AF3>2.01,4,IF(AF3>1.42,3,IF(AF3>0.83,2,IF(AF3>0.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

 

[frankobl3]

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]

You need to fix this formula:

=IF(AF3>2.6,5,IF(AF3>2.01,4,IF(AF3>1.42,3,IF(AF3>0.83,2,IF(AF3>0.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),"")

 

[Ron Rosenfeld]

=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