Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Requested on Formula
Here's my challenge:
If a cell (not the cell I'm writing the formula in) has a $amount that is greater than $10,000, but less than $19,999, then I would like the letter "C" to show up in the cell that I'm writing the formula in. However, if the cell has a $amount that is greater than $20,000, but less than $29,999, then I would like the letter "B" to show up in the cell. Next, if the cell has a $amount that is greater than $30,000, then I would like the letter "A" to show up in the cell. Lastly, if the cell has a $amount that is less than $10,000, then I would like the words "Not an EDPR Acct" to show up in the cell. Can anyone help me craft a formula that would do this? Thanks very much, Rich Rosier |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Requested on Formula
Try this:
=IF(A1=30000,"A",IF(A1=20000,"B",IF(A1=10000,"C ","Not an EDPR Acct"))) I have assumed that the cell with the monetary value in is A1 - adjust accordingly. Note that your description does not cover what to do when the cell contains exactly 10,000, or 20,000 or 30,000 - I've assumed that these are part of the higher range. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Requested on Formula
I think something like this would work:
For a value in A1 B1: =LOOKUP(A1,{0,10000,20000,30000},{"Not an EDPR Acct","C","B","A"}) Alternatively, you could build a table of those same tiered values and reference that table with a VLOOKUP instead. Refs Col_C Col_D Row_1 0 Not an EDPR Acct Row_2 10000 C Row_3 20000 B Row_4 30000 A B1: =VLOOKUP(A1,$C$1:$D$4,2,1) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Rich Rosier" wrote: Here's my challenge: If a cell (not the cell I'm writing the formula in) has a $amount that is greater than $10,000, but less than $19,999, then I would like the letter "C" to show up in the cell that I'm writing the formula in. However, if the cell has a $amount that is greater than $20,000, but less than $29,999, then I would like the letter "B" to show up in the cell. Next, if the cell has a $amount that is greater than $30,000, then I would like the letter "A" to show up in the cell. Lastly, if the cell has a $amount that is less than $10,000, then I would like the words "Not an EDPR Acct" to show up in the cell. Can anyone help me craft a formula that would do this? Thanks very much, Rich Rosier |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help Requested on Formula
On Sun, 2 Apr 2006 12:20:01 -0700, Rich Rosier
wrote: Here's my challenge: If a cell (not the cell I'm writing the formula in) has a $amount that is greater than $10,000, but less than $19,999, then I would like the letter "C" to show up in the cell that I'm writing the formula in. However, if the cell has a $amount that is greater than $20,000, but less than $29,999, then I would like the letter "B" to show up in the cell. Next, if the cell has a $amount that is greater than $30,000, then I would like the letter "A" to show up in the cell. Lastly, if the cell has a $amount that is less than $10,000, then I would like the words "Not an EDPR Acct" to show up in the cell. Can anyone help me craft a formula that would do this? Thanks very much, Rich Rosie Just another method: =CHOOSE(MIN(INT(A1/10000)+1,4),"Not an EDPR Acct","C","B","A") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |