Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated if then formula...need help
Here's what I'm looking for: A way to have a value generated below reflect the letter that corresponds with a dollar tier listed below. (the tiers change, the letters are fixed.) Tier Low High A $1,500.00 $4,499.99 B $4,500.00 $7,499.99 C $7,500.00 $16,999.99 D $17,000.00 $26,499.99 E $26,500.00 $9,999,999.00 The Tier letters in the first column are set, the users input the tier dollar amounts. Below there is a cell that spits out a total amount and I want it to display the letter for the corresponding tier. No matter what I do I can't get it to work. Basically I want it to say something like this: If E37 is equal to or greater than the cell that corresponds with 1500 but less than the cell that corresponds with 4500 than display the cell that shows Tier A, If it's equal to or greater than 4500 and less than the cell with 7500 than show B, etc. so it takes that one number and spits out what letter tier and if its not in any tier it shows no value or -. Is this possible and what's the easiest way? -- LincAg ------------------------------------------------------------------------ LincAg's Profile: http://www.excelforum.com/member.php...o&userid=34798 View this thread: http://www.excelforum.com/showthread...hreadid=545542 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated if then formula...need help
Try:
Table in A1:C6 Place this in required cell: =IF(ISERROR(INDEX(A2:A6,MATCH(E37,$B$2:$B$6,1)))," ",INDEX(A2:A6,MATCH(E37,$B$2:$B$6,1))) HTH "LincAg" wrote: Here's what I'm looking for: A way to have a value generated below reflect the letter that corresponds with a dollar tier listed below. (the tiers change, the letters are fixed.) Tier Low High A $1,500.00 $4,499.99 B $4,500.00 $7,499.99 C $7,500.00 $16,999.99 D $17,000.00 $26,499.99 E $26,500.00 $9,999,999.00 The Tier letters in the first column are set, the users input the tier dollar amounts. Below there is a cell that spits out a total amount and I want it to display the letter for the corresponding tier. No matter what I do I can't get it to work. Basically I want it to say something like this: If E37 is equal to or greater than the cell that corresponds with 1500 but less than the cell that corresponds with 4500 than display the cell that shows Tier A, If it's equal to or greater than 4500 and less than the cell with 7500 than show B, etc. so it takes that one number and spits out what letter tier and if its not in any tier it shows no value or -. Is this possible and what's the easiest way? -- LincAg ------------------------------------------------------------------------ LincAg's Profile: http://www.excelforum.com/member.php...o&userid=34798 View this thread: http://www.excelforum.com/showthread...hreadid=545542 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated if then formula...need help
Create a table in M1:N5 like so
1500 A 4500 B 7500 C 17000 D 26500 E then use =VLOOKUP(E37,$M$1:$N$5,2) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "LincAg" wrote in message ... Here's what I'm looking for: A way to have a value generated below reflect the letter that corresponds with a dollar tier listed below. (the tiers change, the letters are fixed.) Tier Low High A $1,500.00 $4,499.99 B $4,500.00 $7,499.99 C $7,500.00 $16,999.99 D $17,000.00 $26,499.99 E $26,500.00 $9,999,999.00 The Tier letters in the first column are set, the users input the tier dollar amounts. Below there is a cell that spits out a total amount and I want it to display the letter for the corresponding tier. No matter what I do I can't get it to work. Basically I want it to say something like this: If E37 is equal to or greater than the cell that corresponds with 1500 but less than the cell that corresponds with 4500 than display the cell that shows Tier A, If it's equal to or greater than 4500 and less than the cell with 7500 than show B, etc. so it takes that one number and spits out what letter tier and if its not in any tier it shows no value or -. Is this possible and what's the easiest way? -- LincAg ------------------------------------------------------------------------ LincAg's Profile: http://www.excelforum.com/member.php...o&userid=34798 View this thread: http://www.excelforum.com/showthread...hreadid=545542 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
complicated if then formula...need help
That first one worked out great. I'll play with the second one later. Great job! - Thanks!!! -- LincAg ------------------------------------------------------------------------ LincAg's Profile: http://www.excelforum.com/member.php...o&userid=34798 View this thread: http://www.excelforum.com/showthread...hreadid=545542 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated formula help required | Excel Discussion (Misc queries) | |||
Complicated conditional "countif" formula | Excel Discussion (Misc queries) | |||
Replacing MCONCAT with CONCAT_RANGE (in a complicated formula) | Excel Worksheet Functions | |||
Complicated Pie Chart formula | Charts and Charting in Excel | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) |