Calculated values in a cell

  • Thread starter Thread starter frankobl3
  • Start date Start date
F

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
 
Remove *all* the quotes from around any numbers.
=IF(AG3=5,"2008",.....
=IF(AF3>2.6,"5",.....
Click to expand...

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.
 
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
 
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),"")
 
=IF(AG3=5,"2008",IF(AG3=4,"2009",IF(AG3=3,"2010",IF(AG3=2,"2011",IF(AG3=1,"2012")))))
Click to expand...

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
 
Back
Top