| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculated Values | Excel Worksheet Functions | |||
| Exporting calculated values | Setting up and Configuration of Excel | |||
| Operating on Non Calculated Values | Excel Discussion (Misc queries) | |||
| how to change a calculated cell to = the calculated value | Excel Discussion (Misc queries) | |||
| do not graph zero calculated values. | Charts and Charting in Excel | |||
Linear Mode
